SQLServer的trigger之使用

触发器trigger主要用作两个表间的insert，delete，update的同步，不需要手工启动，只需要事件触发。做了trigger的表只需insert，delete，update，另外的表也会同步执行，比较方便好用。
创建trigger过程中，需要注意的是有两个默认的表inserted和deleted。

1.插入操作（Insert）
Inserted表有数据，Deleted表无数据

2.删除操作（Delete）
Inserted表无数据，Deleted表有数据

3.更新操作（Update）
Inserted表有数据（新数据），Deleted表有数据（旧数据）

因业务需要，我只做insert和update的trigger

创建trigger语句

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[trigger的表] 

FOR INSERT, UPDATE, DELETE 

AS    select * from sysfiles 

我做的demo

USE [SMPS_DEV]
GO
/****** Object:  Trigger [dbo].[vpas_interface_from_PCS_header_NSC_retention_insert]    Script Date: 2020/9/8 11:26:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[vpas_interface_from_PCS_header_NSC_retention_insert] ON [dbo].[vpas_interface_from_PCS_header_NSC_retention] 

FOR INSERT, UPDATE

AS   
BEGIN

SET NOCOUNT ON;
DECLARE @all_count INT
-- insert
IF EXISTS (
            SELECT 1
            FROM inserted
            )
        AND NOT EXISTS (
            SELECT 1
            FROM deleted
            )
	BEGIN
	SELECT @all_count = count(*)
			FROM inserted a
				,vpas_interface_from_PCS_header_NSC_retention_all b
			WHERE a.contract_num = b.contract_num


	
	IF @all_count=0
	BEGIN
	 INSERT INTO vpas_interface_from_PCS_header_NSC_retention_all(
		contract_num,
		payment_seq_num,
		payment_type,
		status,
		reject_remark,
		qs_report_num,
		apply_release_retention_amount,
		cum_retention_hold_amount,
		create_user,
		create_date
		)
		SELECT 
		contract_num,
		payment_seq_num,
		payment_type,
		status,
		reject_remark,
		qs_report_num,
		apply_release_retention_amount,
		cum_retention_hold_amount,
		create_user,
		create_date
		FROM vpas_interface_from_PCS_header_NSC_retention
	END
	ELSE
			BEGIN
				RAISERROR (
						'User exists!'
						,18
						,1
						)
				WITH NOWAIT
			END
	END
 -- update
ELSE
BEGIN
	SELECT @all_count = COUNT(*)
	FROM inserted i
	WHERE NOT EXISTS(
	
		SELECT 0 
		FROM vpas_interface_from_PCS_header_NSC_retention_all v
		WHERE i.contract_num = v.contract_num
	
	)
	IF @all_count =0
	BEGIN
		UPDATE  v
		SET contract_num = i.contract_num,
			payment_seq_num = i.payment_seq_num,
			payment_type = i.payment_type,
			status = i.status,
			reject_remark = i.reject_remark,
			qs_report_num = i.qs_report_num,
			apply_release_retention_amount = i.apply_release_retention_amount,
			cum_retention_hold_amount = i.cum_retention_hold_amount,
			create_user = i.create_user,
			create_date = i.create_date

			FROM inserted i,
				 vpas_interface_from_PCS_header_NSC_retention_all v
			WHERE i.contract_num = v.contract_num

	END
	ELSE
        BEGIN
            RAISERROR (
                    'User not exists!'
                    ,18
                    ,2
                    )
            WITH NOWAIT
        END


END

END

最后

以上就是可靠花卷为你收集整理的SQLServer的trigger之使用的全部内容，希望文章能够帮你解决SQLServer的trigger之使用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。