Example 1:update
Use [database name]
GO
/****** Object:trigger [dbo]. [Trigger name] Script date:05/08/2014 12:40:25 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo]. [T_ Trigger Name] on [dbo]. [Table name]
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);
--Query to local replication
--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 table name VALUES (@Id,.....);
COMMIT TRANSACTION
END
Example 2:insert
Use [database name]
GO
/****** Object:trigger [dbo]. [Trigger name] Script date:05/08/2014 12:47:46 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo]. [Trigger name] on [dbo]. [Table name]
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 ...)
--inserting data into the Insert User Temp table
INSERT into DBO.TRANSFER_DATA_INSERT_TEMP2 VALUES (@Id ...);
COMMIT TRANSACTION
--Insert statements for trigger here
END
Example 3:d elete
Use [database name]
GO
/****** Object:trigger [dbo]. [Trigger name] Script date:05/08/2014 12:51:21 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo]. [Trigger name] on [dbo]. [Table name]
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,....)
--inserting data into the Insert User Temp table
INSERT into dbo. Table name VALUES (@Id);
COMMIT TRANSACTION
--Insert statements for trigger here
END