Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Hungary
    Posts
    5

    Unhappy Unanswered: table trigger for log - help

    Hi!

    I'm newbie and want to create a table trigger to input the sysdate and user string into same table after all insert and update event. I made this try:

    CREATE OR REPLACE TRIGGER LOGTRIG
    AFTER INSERT OR UPDATE OF CENZURASZAM
    ON HP_HIANYPOTLAS
    FOR EACH ROW
    begin
    update hp_hianypotlas h
    set h.rdate = sysdate,
    h.ruser = 'tryinuser';
    where h.id=h.id;
    end;
    /

    It doesn't matter the last line with "where" enabled or commented out, the result is all insert or update event change all "rdate" and "ruser" value in all record.
    Can someone help me?

    Thanks and regards:
    Tamas

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: table trigger for log - help

    Yes, well the condition "where h.id=h.id" is like "where 1=1", it is ALWAYS true! It says "Where the id of the hp_hianypotlas record (alias h) is equal to the id of the hp_hianypotlas record (alias h)".

    Anyway, the correct syntax for this does not involve an update at all:

    CREATE OR REPLACE TRIGGER LOGTRIG
    BEFORE INSERT OR UPDATE OF CENZURASZAM
    ON HP_HIANYPOTLAS
    FOR EACH ROW
    begin
    :NEW.rdate = sysdate;
    :NEW.ruser = 'tryinuser';
    end;
    /

    Note that this is a BEFORE not an AFTER trigger.

  3. #3
    Join Date
    Jan 2003
    Location
    Hungary
    Posts
    5

    Smile

    Thank your quick response andrewst! It works (of course...)

    Thanks again:
    Tamas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •