MySQL触发器写入Sqlseever_sqlserver 触发器实例

实例1:update

USE [数据库名称]

GO

/****** Object: Trigger [dbo].[触发器名称] Script Date:

05/08/2014 12:40:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER

ON

GO

ALTER TRIGGER [dbo].[T_触发器名称] ON [dbo].[表名称]

FOR

UPDATE

AS

DECLARE

@Id VARCHAR(32),

@remain_cost

DECIMAL(10,3),

@remain_flow DECIMAL(10,3),

@valid_time

DATETIME;

BEGIN

SET XACT_ABORT ON

BEGIN

TRANSACTION

--SELECT @Id = customer_no, @remain_cost = Balance,@remain_flow =

(MAX_VOLUME - USED_VOLUME) FROM INSERTED;

--insert

openrowset(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘select * from

mysql_authon.Consumer ‘) values(@Id,......)

SELECT @Id = customer_no,

@remain_cost = Balance,@remain_flow = (MAX_VOLUME - USED_VOLUME) FROM

INSERTED;

-- SET @Id = (SELECT customer_no FROM INSERTED);

-- SET

@remain_cost = (SELECT Balance FROM INSERTED);

--SET @remain_flow = (SELECT

(MAX_VOLUME - USED_VOLUME) FROM INSERTED);

-- 查询对本地复制

--SET @sql =

-- SET @type = (SELECT * FROM

OPENROWSET(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘SELECT UserType_id FROM ....

@Id‘))

-- SET @base_cost = (SELECT * FROM

OPENROWSET(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘SELECT Consumer_basecost FROM

.... @Id‘))

-- DELETE FROM

OPENROWSET(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘select * from ....= @Id

‘)

-- INSERT

openrowset(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘select * from

mysql_authon.Consumer ‘) values(@Id,@type,.....)

INSERT INTO 表名称

VALUES(@Id,.....);

COMMIT TRANSACTION

END

实例2:insert

USE [数据库名称]

GO

/****** Object: Trigger [dbo].[触发器名称] Script Date:

05/08/2014 12:47:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER

ON

GO

ALTER TRIGGER [dbo].[触发器名称] ON [dbo].[表名称]

FOR

INSERT

AS

DECLARE

@Id VARCHAR(16),

@remain_cost

DECIMAL(10,3),

@remain_flow DECIMAL(10,3),

@valid_time

DATETIME;

BEGIN

SET XACT_ABORT ON

-- SET NOCOUNT ON added to

prevent extra result sets from

-- interfering with SELECT statements.

BEGIN TRANSACTION

SELECT @Id = customer_no........ FROM

INSERTED;

-- insert

openrowset(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘select * from

mysql_authon.Consumer ‘) values(@Id.....)

-- 将数据插入到插入用户临时表

INSERT

INTO dbo.transfer_data_insert_temp2 VALUES(@Id.....);

COMMIT

TRANSACTION

-- Insert statements for trigger here

END

实例3 :delete

USE [数据库名称]

GO

/****** Object: Trigger [dbo].[触发器名称] Script Date:

05/08/2014 12:51:21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER

ON

GO

ALTER TRIGGER [dbo].[触发器名称] ON [dbo].[表名称]

FOR

DELETE

AS

DECLARE

@Id VARCHAR(16);

BEGIN

SET XACT_ABORT

ON

-- SET NOCOUNT ON added to prevent extra result sets from

--

interfering with SELECT statements.

BEGIN TRANSACTION

SELECT @Id =

customer_no FROM deleted;

-- insert

openrowset(‘MSDASQL‘,‘smysql‘;‘root‘;‘123456‘,‘select * from

mysql_authon.Consumer ‘) values(@Id,....)

-- 将数据插入到插入用户临时表

INSERT

INTO dbo.表名称 VALUES(@Id);

COMMIT TRANSACTION

-- Insert statements for

trigger here

END