• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1166
  • Last Modified:

Can't update table 'activityinst' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Hi,
  i have a trigger in mssql like below

and we migrated it to mysql like this:
CREATE TRIGGER UPDATE_ACTIVITYINST AFTER UPDATE ON ACTIVITYINST FOR EACH ROW
BEGIN
    DECLARE id integer;
    set id= new.ID;
        UPDATE ACTIVITYINST SET STATETIME = GETDATE() WHERE ACTIVITYINST.ID = id;

END

but when i tried to test, i entered a new record to ACTIVITYINST table.
and again i tried to update one that row. iam getting "Can't update table 'activityinst' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
error.
please let me know where im doing worng and what to do.
please reply soon,it is very urgent
Regards
vijji
0
vijji_lakshmi
Asked:
vijji_lakshmi
1 Solution
 
Steve BinkCommented:
See here:

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Specifically:

----------------------------------------------------
You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.
----------------------------------------------------

Also, you cannot use this in an AFTER trigger.   You'll need to use a BEFORE trigger for this strategy:

set new.STATETIME=GETDATE();
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now