Converting a MySql trigger to a SQL Server 2008 R2 trigger
So I needed to convert a MySQL database to a SQL Server 2008 R2 database. To do this I used Microsoft's new SSMA MySQL tool. Everything was converted fine except for the triggers, it kept crashing when converting t开发者_运维知识库hem.
First MySQL trigger:
CREATE
DEFINER=`root`@`localhost`
TRIGGER `dbo`.`table_A_trig`
BEFORE INSERT ON `dbo`.`table_A`
FOR EACH ROW
BEGIN
/* Logic here */
END
Second MySQL trigger:
CREATE
DEFINER=`root`@`localhost`
TRIGGER `dbo`.`table_A_trig_Update`
AFTER UPDATE ON `dbo`.`table_A`
FOR EACH ROW
BEGIN
/* Logic Here */
END
How should I rewrite them to be able to create them in SQL Server 2008?
Thanks guys!
A couple of differences to point out.
SQL Server doesn't have a "before" trigger, so you'd have to convert the first as an INSTEAD OF trigger and manually perform the insert operation.
SQL Server triggers use the special INSERTED (equivalent to NEW in MySQL) and DELETED (equivalent to OLD in MySQL) tables.
So your CREATE TRIGGER syntax would look something like:
CREATE TRIGGER dbo.table_A_trig
ON dbo.table_A INSTEAD OF INSERT
AS
BEGIN
/* logic here */
/* Manually perform the insert operation */
INSERT INTO dbo.table_A
(column_list)
SELECT column_list
FROM INSERTED
END
CREATE TRIGGER dbo.table_A_trig_Update
ON dbo.table_A FOR UPDATE
AS
BEGIN
/* logic here */
END
精彩评论