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,127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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