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,123 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

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