Solved

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

Posted on 2008-10-03
1
1,106 Views
Last Modified: 2012-05-05
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
Comment
Question by:vijji_lakshmi
1 Comment
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 22639304
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Creating and Managing Databases with phpMyAdmin in cPanel.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now