Skip to Content
avatar image
Former Member

syntax Error while creating trigger

This is the error i am getting related to its syntax, any help on this?

trg.png (34.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 30, 2016 at 12:15 PM

    There are several issues in that create trigger statement:

    • The "trigger action time" (after update) has to be placed before the "subject table name" (LOGIN).
    • The transition list referencing is wrong ("... referencing old as _old ..." -> "referencing old row _old")
    • The logic applied by that trigger cannot be applied, because the subject table cannot be modified within a trigger (and especially not with that incorrect syntax you are trying to use).

    A valid trigger syntax for your example would be:

    CREATE TRIGGER trg_count 
    AFTER UPDATE
    ON LOGIN
    REFERENCING OLD ROW _old
    FOR EACH ROW
    BEGIN
      -- valid trigger body to be inserted
    END;
    

    Maybe the following "before update" trigger logic is helpful as you are trying to increase the value for column "_usr_cnt" for each update:

    CREATE TRIGGER trg_count 
    BEFORE UPDATE
    ON LOGIN
    REFERENCING NEW ROW _new
    FOR EACH ROW
    BEGIN
      _new._usr_cnt = :_new._usr_cnt + 1;
    END;
    

    Regards,
    Florian

    PS: By the way, I would not create any tables triggers in the SYSTEM schema as part of a good architecture.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank You so much Florian, for your guidance, I am now practicing in a different schema and looking forward to learn more from the people of this community.

      Regards.

      Roshan Jha