SQL Creating a trigger with after update

Question

I have two tables. One table is restaurant, and the other is restaurant_changes. Restaurant contains attributes, id, name, address, city, state, zip. While restaurant_changes contains all of those but also a timestamp.

Whenever a change is made to restaurant I want it to be noted in the restaurant_changes table. It does it by storing a timestamp and the rest of the row before changes. So if restaurant name changes from A to B in restaurant_changes, the name A will be stored.

Here is what I'm trying to do and I'm hoping I can get steered in the correct direction.

create trigger change_restaurant after update on restaurant
referencing old table as otab
referencing new table as tab
Declare @today DATETIME = SYSDATETIME()
for each row
    insert into restaurant_changes 
    select(@today, otab.id, otab.name, otab.address, otab.city, otab.state, otab.zip
    from otab
    inner join ntab
    on otab.id = ntab.id)

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Nov 13 '13 at 12:49
I'm just interested in the SQL and not specifically any database. In other words I don't need it to run. I'm just trying to learn it. — Spark323, Nov 13 '13 at 12:58
This might give you some ideas, http://stackoverflow.com/questions/1962398/creating-audit-triggers-in-sql-server — Dan Bracuk, Nov 13 '13 at 13:11
Triggers tend to be very much **vendor-specific** so I don't think you can really do this without knowing what database you're going to use it for — marc_s, Nov 13 '13 at 15:02

wvdz · Answer 1 · 2013-11-13T13:15:12.453

Looks like this is what you want (just mimicking your assumed syntax):

create trigger change_restaurant after update on restaurant
referencing old as old
Declare @today DATETIME = SYSDATETIME()
for each row
    insert into restaurant_changes values
    (@today, old.id, old.name, old.address, old.city, old.state, old.zip)

Referring to the old_table doesn't make much sense in combination with 'for each row'. If you want to use old_table, you have should declare 'for each statement'.

SQL Creating a trigger with after update

1 Answers1