syntax Error while creating trigger

[roshan89]

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

[pfefferf · Accepted solution]

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.

[roshan89 · Accepted solution]

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