Re: trigger updating a master table from detail table

As another poster has pointed out, you need the colon ':' before OLD and NEW when they appear in the body of the trigger.

As another issue though, be careful with this type of trigger - it is very easy to end up coding a system that spends lots of time waiting for commits, and running into deadlocks.

e.g.
If I update an EVENEMENT for ETABLISSEMENT A, whilst you update an EVENEMENT for ETABLISSEMENT B, and then I update an EVENEMENT for ETABLISSEMENT B, I will have to wait for you to commit.

If, instead of committing, you try to update an EVENEMENT for ETABLISSEMENT A, Oracle will spot the deadlock and raise an ORA-00060 in my session; at which point I will have to rollback and retry.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Philippe Makowski wrote in message <9jkivt$4f7$1_at_wanadoo.fr>...


>this code doesn't compil any idea ?

>EVENEMENT is a détail table of ETABLISSEMENT.

>

>

>CREATE TRIGGER DATE_MAJ_ETAB_EVENEMENTS

>  AFTER DELETE OR UPDATE OR INSERT ON EVENEMENT FOR EACH ROW

>WHEN (OLD.NUM_ETAB IS NOT NULL)

>BEGIN

>  UPDATE ETABLISSEMENT SET DATE_MAJ_ETAB = SYSDATE

>  WHERE NUM_ETAB = OLD.NUM_ETAB

>END

>;

>thanks

>

>