I need a trigger that updates a datetime field with timestamp after the modification of a record. Who can help me? Regards Franz

asked 17 Mar, 12:20

Franz_Stf's gravatar image

Franz_Stf
10328
accept rate: 0%

FWIW, a good question, and at least Breck and me have learnt something new:)

(19 Mar, 04:17) Volker Barth

You do not need a trigger for that, simply use a timestamp column with DEFAULT TIMESTAMP or the UTC variant. Each time the according row gets updated, the column will be set automatically, unless you specify a value for it in the SET clause of the UPDATE statement.

permanent link

answered 17 Mar, 15:55

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

edited 18 Mar, 04:33

The column should not always be updated, but only if the record has changed. Regards Franz

(18 Mar, 04:13) Franz_Stf
Replies hidden

only if the record has changed

What do you mean by that? Only if the contents of the row has changed? I.e. if the row is updated but the contents is still unchanged, that timestamp should not be modified?

(18 Mar, 04:34) Volker Barth

Only if the record has changed should the datetime field of the record be filled with timestamp. Regards Franz

(18 Mar, 04:40) Franz_Stf

That untested code snippet should help...

Some information:

  • After update triggers are only fired when the contents of the according row has been changed, i.e. something like "UPDATE MyTable SET MyField = MyField WHERE MyPK = 1;" would not fire an after update trigger (well, unless there's a before update trigger that modifieds the row behind the curtains...).
  • After update triggers can fire recursively so one needs to prevent that. Here, the trigger is only run when the contents of the according column (here named "dateChanged") has not been changed by the UPDATE statement itself. - When the trigger has run once for the according row, it is called again but then the value is different and the WHEN condition is not fulfilled anymore.
  • I generally prefer row-level triggers as they are (IMHO) way easier to code than statement-level triggers.
  • As Breck has pointed out, my first attempt to modify the column record-wise within the after update trigger fails with SQLCODE -191. So you have to issue a "real" UPDATE statement there.
create trigger tr_update_dateChanged
after update
   on MyTable
   referencing old as old_row
      new as new_row
for each row 
when (old_row.dateChanged is not distinct from new_row.dateChanged)
begin
   update MyTable set dateChanged = current timestamp
   where MyPk = new_row.MyPk;
end;
permanent link

answered 18 Mar, 05:17

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

edited 19 Mar, 04:16

Thank you very much. I will test this... Regards Franz

(18 Mar, 05:34) Franz_Stf

I'm not sure, but I think the example now refers to a field. The date should be updated if the record has changed. Regards Franz

(18 Mar, 06:27) Franz_Stf
Replies hidden

Have you tested that (untested) code? I'm not sure what you mean by your last comment...

The example refers to a AFTER UPDATE trigger that should get fired when any column of the according row has been modified (i.e. when any of the columns has a different contents than before). In my understanding, that is your goal. If then the WHEN condition is true (i.e. when the particular column "dateChanged" has not been modified), it is then set to the current timestamp.

This UPDATE will modify the row, so the trigger is called again. Then, the WHEN condition is not fulfilled as the new and old value of the "dateChanged" column are different, and the trigger does nothing.

At least it should work that way.

(18 Mar, 06:41) Volker Barth

According to "The Book"... An after row UPDATE trigger is not fired for a row where no column values actually changed in value. :)

(18 Mar, 08:36) Breck Carter
Replies hidden

Hm, is that different from what I've tried to say? Do you realte to changes made via the SET clause vs. done within before triggers?

(For sure, I had checked within "The Book":))

(18 Mar, 08:45) Volker Barth
1

Ooops, I misread the "when" clause in your trigger.

Alas, the third point from "The Book" says the "set new_row.dateChanged" isn't going to work (SQLCODE -191)...

After row triggers work almost the same way as before row triggers, 
with three differences:

An after row UPDATE trigger is not fired for a row where no column values 
actually changed in value.

An after row UPDATE OF trigger is not fired for a row where none of 
the columns named in the UPDATE OF clause actually changed in value.

It is not possible to modify the values in the REFERENCING NEW AS structure 
because it’s too late, the row operation has already been performed. 

Here's a test...

CREATE TABLE MyTable ( 
   pkey         INTEGER NOT NULL PRIMARY KEY,
   data         INTEGER NOT NULL DEFAULT 0,
   dateChanged   TIMESTAMP NOT NULL DEFAULT '1900-01-01' );

create trigger tr_update_dateChanged
after update
   on MyTable
   referencing old as old_row
      new as new_row
for each row 
when (old_row.dateChanged is not distinct from new_row.dateChanged)
begin
   set new_row.dateChanged = current timestamp;
end;

INSERT MyTable ( pkey, data ) VALUES ( 1, 1 );
COMMIT;
UPDATE MyTable SET data = 1; -- does not fire trigger
COMMIT;
SELECT 'first UPDATE', * FROM MyTable;
UPDATE MyTable SET data = 2; -- fires trigger but it fails with SQLCODE -191
COMMIT;
SELECT 'second UPDATE', * FROM MyTable;
(18 Mar, 09:16) Breck Carter

Ah I see,

so the following trigger code with a "real UPDATE" within the code does the trick:

create or replace trigger tr_update_dateChanged
after update
   on MyTable
   referencing old as old_row
      new as new_row
for each row 
when (old_row.dateChanged is not distinct from new_row.dateChanged)
begin
   update MyTable set dateChanged = current timestamp
   where pkey = new_row.pkey;
end;

Note that explicitly setting the dateChanged column to a different value accepts that specified value (here, yesterday):

-- does not fire trigger
UPDATE MyTable SET data = 3, dateChanged = dateadd(dd, -1, current timestamp);
COMMIT;
SELECT 'third UPDATE', * FROM MyTable;
(18 Mar, 09:50) Volker Barth

Yeah, that's still easier than a stupid statement-level trigger.

(18 Mar, 10:20) Breck Carter
showing 3 of 8 show all flat view

This may be what you're looking for; it's a trigger that fires once for each UPDATE statement, and has to deal with old_row and new_row as (possibly multi-row) temporary tables rather than single-row structures, and a FOR cursor loop is used to prevent runaway recursion.

Note that the WHEN clause can't be used for a statement-level trigger, but it can be used in the FOR WHERE clause.

( Funny aside... until today I had no idea IS DISTINCT FROM existed, so thank you Volker for pointing that out :)

CREATE TABLE MyTable ( 
   pkey         INTEGER NOT NULL PRIMARY KEY,
   data         INTEGER NOT NULL DEFAULT 0,
   dateChanged  TIMESTAMP NOT NULL DEFAULT '1900-01-01' );

create trigger tr_update_dateChanged
after update
   on MyTable
   referencing old as old_row
      new as new_row
for each statement 
begin
   FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
   SELECT MyTable.pkey AS @pkey
     FROM MyTable
             INNER JOIN new_row
                     ON new_row.pkey = MyTable.pkey
    WHERE MyTable.dateChanged IS NOT DISTINCT FROM new_row.dateChanged
   FOR READ ONLY
   DO
      UPDATE MyTable
         SET MyTable.dateChanged = current timestamp
       WHERE MyTable.pkey = @pkey;
   END FOR;
end;

INSERT MyTable ( pkey, data ) VALUES ( 1, 1 );
INSERT MyTable ( pkey, data ) VALUES ( 2, 1 );
COMMIT;

-- UPDATE with same MyTable.data values.
UPDATE MyTable SET data = 1; -- the trigger should NOT change dateChanged  
COMMIT;
SELECT 'first UPDATE', * FROM MyTable;

-- UPDATE with different MyTable.data values.
UPDATE MyTable SET data = 2; -- the trigger SHOULD change dateChanged  
COMMIT;
SELECT 'second UPDATE', * FROM MyTable;

-- UPDATE with different MyTable.dateChanged values.
UPDATE MyTable SET dateChanged = '2000-01-01'; -- the trigger should NOT change dateChanged   
COMMIT;
SELECT 'third UPDATE', * FROM MyTable;

1 row(s) inserted
1 row(s) inserted
2 row(s) updated
'first UPDATE'        pkey        data dateChanged             
-------------- ----------- ----------- ----------------------- 
first UPDATE             1           1 1900-01-01 00:00:00.0   
first UPDATE             2           1 1900-01-01 00:00:00.0   
(2 rows)

2 row(s) updated
'second UPDATE'        pkey        data dateChanged             
--------------- ----------- ----------- ----------------------- 
second UPDATE             1           2 2019-03-18 10:07:06.519 
second UPDATE             2           2 2019-03-18 10:07:06.52  
(2 rows)

2 row(s) updated
'third UPDATE'        pkey        data dateChanged             
-------------- ----------- ----------- ----------------------- 
third UPDATE             1           2 2019-03-18 10:07:06.524 
third UPDATE             2           2 2019-03-18 10:07:06.524 
(2 rows)
permanent link

answered 18 Mar, 10:14

Breck%20Carter's gravatar image

Breck Carter
29.1k482647949
accept rate: 21%

edited 18 Mar, 10:18

Thank you very much. I will test this... Regards Franz

(18 Mar, 11:59) Franz_Stf

I tested it, it works under SAP SQL Anywhere. I can't use it because I have to stay platform independent. Too bad, it would have been an elegant method. But I want to thank everyone who supported me here. Greetings Franz

permanent link

answered 20 Mar, 06:51

Franz_Stf's gravatar image

Franz_Stf
10328
accept rate: 0%

I can't use it because I have to stay platform independent.

Hm, that would have been helpful to know beforehand...

How "platform independent" do you need to be? AFAIK, trigger syntax and capabilities are very different on different database systems so I guess it is really hard to find a "general trigger-based solution". However, as SQL Anywhere supports Transact-SQL, it may be possible to find a trigger-based approach working with, say SQL Anywhere and ASE or MS SQL Server...

(20 Mar, 07:49) Volker Barth

I need to support SAP SQL Anywhere, Oracle, MSSQL and IBM DB2. Now I just have to make a software solution.

!--- Save modification date/time and user ------------------------
CASE SELF.Request
OF ChangeRecord
    if ART:RECORD ~= ARTIKEL_LI then
             Art:Aenderungsdatum_DATE = Today()
         Art:Aenderungsdatum_TIME = Clock()
         ART:User_Aenderung       = GetUserName()
    end
END

Regards Franz

(20 Mar, 09:39) Franz_Stf
Replies hidden
1

> SAP SQL Anywhere, Oracle, MSSQL and IBM DB2

You have two choices, one of them easier and more rewarding than the other

Continue looking for a common denominator amoung SQL Anywhere, Oracle, MSSQL and IBM DB2, or

Get rid of Oracle, MSSQL and IBM DB2.

(20 Mar, 12:49) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×66

question asked: 17 Mar, 12:20

question was seen: 244 times

last updated: 20 Mar, 12:49