MySQL Forums
Forum List  »  Triggers

Triggers are not working properly after converting code from SQL SERVER
Posted by: tcee sutton
Date: February 11, 2013 08:55AM

I'm in the process of migrating Triggers from SQL Server to Mysql. Based on the Table created in SQL SERVER, Some of the columns are NULL until the User inputs info into table. When you run the table in SQL SERVER Actionid is auto-incremented and the rest of the fields do not let you put in erroneous information. However, I had to convert and migrate the SQL SERVER Trigger to MYSQL and although my table doesn't show any errors, when I run the table in MYSQL it is allowing me to put ANYTHING in the field and creating erroneous entries.

Can someone Please look at my code and tell me where I am going wrong. I truly appreciate it.

SQL SERVER TRIGGER:
USE [a7itm]
GO
/****** Object: Trigger [dbo].[tr_act_delete] Script Date: 02/11/2013 06:45:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_act_delete] ON [dbo].[tr_action]
FOR DELETE AS
DECLARE @id INTEGER, @attachments INTEGER
SELECT
@id = deleted.actionid, @attachments = deleted.attachments
FROM deleted
BEGIN
IF @attachments > 0
BEGIN
DELETE FROM tr_link WHERE linkheaderid IN
(SELECT linkheaderid FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id)
DELETE FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
END
DELETE FROM tr_comment WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
DELETE FROM tr_alert WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
END
--------------------------------------------------------------
MYSQL TRIGGER:

USE `a7itm`;
/****** Object: Trigger [dbo].[tr_act_delete] Script Date: 02/09/2013 17:12:24 ******/
DROP TRIGGER IF EXISTS tr_act_delete;
DELIMITER $$
CREATE TRIGGER `tr_act_delete` AFTER DELETE ON `tr_action`
FOR EACH ROW BEGIN
DECLARE `id` INTEGER;
DECLARE `attachments` INTEGER;
SET
`id` = deleted.actionid, `attachments` = deleted.attachments;

IF `attachments` > 0 THEN
BEGIN
DELETE FROM tr_link WHERE linkheaderid IN
(SELECT linkheaderid FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`);
DELETE FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
END;
DELETE FROM tr_comment WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
DELETE FROM tr_alert WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
END IF;
END$$
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Triggers are not working properly after converting code from SQL SERVER
2934
February 11, 2013 08:55AM


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.