MySQL Forums
Forum List  »  Microsoft SQL Server

can't triggers reference to tables?
Posted by: Chagh
Date: June 25, 2005 04:43AM

hi all,
I've got a trigger in MS SQLServer 2000 and i want to migrate it to MySQL. 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 for MySQL it gives me syntax error.
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")

my trigger in SQLServer is:

SQL Server Code:
"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"


MySQL Code:
"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"


Thanks in advance
Chagh

Options: ReplyQuote


Subject
Written By
Posted
can't triggers reference to tables?
June 25, 2005 04:43AM


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.