将 MySql 触发器转换为 SQL Server 2008 R2 触发器

发布于 2024-10-14 08:54:19 字数 549 浏览 2 评论 0原文

所以我需要将MySQL数据库转换为SQL Server 2008 R2数据库。为此，我使用了 Microsoft 的新 SSMA MySQL 工具。除了触发器之外，一切都转换得很好，转换它们时它一直崩溃。

第一个 MySQL 触发器：

CREATE
DEFINER=`root`@`localhost`
TRIGGER `dbo`.`table_A_trig`
BEFORE INSERT ON `dbo`.`table_A`
FOR EACH ROW
BEGIN
   /* Logic here */ 
END

第二个 MySQL 触发器：

CREATE
DEFINER=`root`@`localhost`
TRIGGER `dbo`.`table_A_trig_Update`
AFTER UPDATE ON `dbo`.`table_A`
FOR EACH ROW
BEGIN
  /* Logic Here */
END

我应该如何重写它们才能在 SQL Server 2008 中创建它们？

谢谢你们！

原文

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 them.

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想你只要分分秒秒 2024-10-21 08:54:19

需要指出一些差异。

SQL Server 没有“before”触发器，因此您必须将第一个触发器转换为 INSTEAD OF 触发器并手动执行插入操作。
SQL Server 触发器使用特殊的 INSERTED（相当于 MySQL 中的 NEW）和 DELETED（相当于 MySQL 中的 OLD）表。

因此，您的 CREATE TRIGGER 语法看起来像：

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

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

回复收藏 0 原文

~没有更多了~