creating a trigger

Posted in 2006

Summary

Danny De Koster got a syntax error creating a trigger that logs updates to a table. Christine Normile gave the correct order: CREATE TRIGGER name UPDATE ... ON table AFTER (action). Art Kagel gave UPDATE OF col ON table AFTER INSERT INTO ..., noting it fires once per statement, and a FOR EACH ROW form with REFERENCING OLD/NEW and the action in parentheses for per-row auditing.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: Triggers, Constraints & Referential Integrity

Danny De Koster — Fri, 29 Dec 2006 17:17:08 +0100 — source: Usenet: comp.databases.informix

hi all!! last question of this year.... I want to use a trigger. Purpose is to keep track of changes in a table. create trigger TRIG1after update <field> on <table> write something in a table before the word after....he gives me a syntax error has occurred.....and I don't know why??? can anybody help me? thanks Danny

↪ replying to Danny De Koster

Christine Normile — Fri, 29 Dec 2006 10:45:03 -0600 — source: Usenet: comp.databases.informix

On Dec 29, 2006, at 10:17 AM, Danny De Koster wrote: > hi all!! > > last question of this year.... > > I want to use a trigger. Purpose is to keep track of changes in a > table. > > create trigger TRIG1> after update <field> on <table> > write something in a table > > before the word after....he gives me a syntax error has > occurred.....and I > don't know why??? Syntax is: Create trigger TRIG1UPDATE <field> on <table> AFTER (write something in a table); > > can anybody help me? > thanks Danny > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Danny De Koster

Art S. Kagel — Fri, 29 Dec 2006 11:52:44 -0500 — source: Usenet: comp.databases.informix

Danny De Koster wrote: > hi all!! > > last question of this year.... > > I want to use a trigger. Purpose is to keep track of changes in a table. > > create trigger TRIG1> after update <field> on <table> > write something in a table > > before the word after....he gives me a syntax error has occurred.....and I > don't know why??? > > can anybody help me? > thanks Danny Did you RTFM? See the Guide to SQL Syntax manual. CREATE TRIGGER trig1 UPDATE OF somefield ON sometable AFTER INSERT INTO someothertable.....; But that will only fire once per UPDATE statement and not per row and will fire even if no rows are updated by the statement. If you want to audit changes to every row updated then: CREATE TRIGGER trig2 UPDATE OF somefield ON sometable REFERENCING OLD AS orig REFERENCING NEW AS chng FOR EACH ROW ( INSERT INTO someothertable ... ); Art S. Kagel

↪ replying to Art S. Kagel

Art S. Kagel — Fri, 29 Dec 2006 11:58:10 -0500 — source: Usenet: comp.databases.informix

Art S. Kagel wrote: > Danny De Koster wrote: > >> hi all!! >> >> last question of this year.... >> >> I want to use a trigger. Purpose is to keep track of changes in a table. >> >> create trigger TRIG1>> after update <field> on <table> >> write something in a table >> >> before the word after....he gives me a syntax error has >> occurred.....and I don't know why??? >> >> can anybody help me? >> thanks Danny > > > Did you RTFM? See the Guide to SQL Syntax manual. > > CREATE TRIGGER trig1 > UPDATE OF somefield ON sometable > AFTER INSERT INTO someothertable.....; OOPS forgot the parenthesis around the INSERT statement above. Art S. Kagel > But that will only fire once per UPDATE statement and not per row and > will fire even if no rows are updated by the statement. If you want to > audit changes to every row updated then: > > CREATE TRIGGER trig2 > UPDATE OF somefield ON sometable > REFERENCING OLD AS orig > REFERENCING NEW AS chng > FOR EACH ROW ( > INSERT INTO someothertable ... > );> > Art S. Kagel >