SQLServer的trigger之使用

115 阅读 0 评论 76 点赞

我是靠谱客的博主可靠花卷，这篇文章主要介绍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语句

复制代码

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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