-1

I have a Boolean column in my database called: IS_SUBMITTED that is by default set to False.

What I would like to do is to have another column in the database called TIME_SUBMITTED that is set to Null by default, but when IS_SUBMITTED is set to True, TIME_SUBMITTED is updated with the current timestamp.

Is this possible to implement? I image it may involve the use of a trigger but I'm quite new to Db2 and I can't quite seem to get it working properly.

Cheers

A. Lewis
  • 67
  • 8

1 Answers1

0

A BEFORE UPDATE trigger is what you need...

The simple solution assumes that IS_SUBMITTED is only ever changed one time from FALSE to TRUE.

create or replace trigger MYTRIGGER
  before update of IS_SUBMITTED on MYTABLE
  referencing 
    new row as n
  set n.time_submited = current_timestamp
;

If IS_SUBMITTED can be changed backed to false, you'd need to decide how to handle that. And use a compound BEGIN/END statement in the trigger with the additional logic. I'll leave that as an exercise.

Lastly note, when asking Db2 questions on SO, it's a good idea to include your platform (LUW, z\OS, IBM i) and version.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thanks for the response @Charles, I see how this is supposed to work. I know it's not the point of SO to be debugging syntax but I get the following error when I try to run that Command: 'An unexpected token "CREATE TRIGGER TIME_SUBMITTED_TRIGGER BEF" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.27.25' – A. Lewis Oct 08 '21 at 19:50
  • Any idea what may be the issue here with the syntax? I'm trying to execute it within the DB2 Cloud UI – A. Lewis Oct 08 '21 at 19:52