MySQL Forums
Forum List  »  Triggers

Triggers can not use reference to tables?
Posted by: Chagh
Date: June 25, 2005 04:36AM

hi all,
I've got a trigger in MS SQLServer 2000 and i want to migrate it to MySQL. my trigger in SQLServer is:

CREATE TRIGGER AddAttach ON dbo._Attachments FOR INSERT

AS

DECLARE @DocCode int
DECLARE @InsertedCount int

SELECT @DocCode = DocCode , @InsertedCount = Count(*)
FROM    INSERTED
GROUP BY DocCode

UPDATE _Documents SET
  AttachCount = AttachCount + @InsertedCount
WHERE   DocCode = @DocCode

UPDATE _Attachments SET
  RelAttNumber = INSERTED.AttNumber
FROM    INSERTED
WHERE INSERTED.AttachmentCode = _Attachments.AttachmentCode

I know we can not reference to tables in triggers(like functions), so what should i do?
(when i execute this "create trigger ..." statement in MySQL by the code below it gives me syntax error:
CREATE TRIGGER AddAttach after INSERT ON _Attachments for each row

begin

DECLARE DocCode int;
DECLARE InsertedCount int;

SELECT  DocCode , Count(*) into DocCode,  InsertedCount
FROM    INSERTED
GROUP BY DocCode;

UPDATE _Documents SET
  AttachCount = AttachCount + InsertedCount
WHERE   DocCode = DocCode;

UPDATE _Attachments SET
  RelAttNumber = INSERTED.AttNumber
FROM    INSERTED
WHERE INSERTED.AttachmentCode = _Attachments.AttachmentCode;

end

the error is:
"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'UPDAT
E _Attachments SET
RelAttNumber = INSERTED.AttNumber
FROM INSERTED
WHE' at line 7"
)

Options: ReplyQuote


Subject
Views
Written By
Posted
Triggers can not use reference to tables?
3588
June 25, 2005 04:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.