在本文中，将介绍如何在 SQL 中使用 AFTER 触发器。

SQL 中的触发器是一种特殊类型的过程，可自动执行、触发或者调用以响应在视图或者表上执行的操作查询。 我们可以在插入、删除和更新或者这些操作的组合上设置触发器。

SQL 中有三种类型的触发器。

• AFTER 触发器
• INSTEAD OF 触发器
• FOR 触发器

SQL 中的 After 触发器在执行操作查询后执行或者触发。 它用于实现参照完整性的概念。 我们使用 CREATE TRIGGER 和 AFTER 子句来创建一个后触发器。

创建copybilltbl表的语句

create table copybilltbl
(
billid int not null identity primary key,
vendorid int not null,
billno varchar(15),
billtotal money,
paymenttotal money,

creadittotal money
)

在Copybilltbl表中插入数据

insert into copybilltbl values (20,'e001',100,100,0.00)
insert into copybilltbl values (21,'e002',200,200,0.00)
insert into copybilltbl values (22,'e003',500,0.00,100)
insert into copybilltbl values (23,'e004',1000,100,100)
insert into copybilltbl values (24,'e005',1200,200,500)

创建Copyoirvenders表

create table copyvendors
(

vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)

)

在Copyoirvenders表中插入数据

insert into copyvendors values (20,'onitroadTestDB','noida','up')
insert into copyvendors values (21,'jack','lucknow','up')
insert into copyvendors values (22,'rahul','kanpur','up')
insert into copyvendors values (23,'malay','Amsterdam','Amsterdam')
insert into copyvendors values (24,'mayank','noida','up')

创建触发器

create trigger copyvendors_del_up
on copyvendors
after delete,update
as
if exists ( select * from deleted join copybilltbl
on deleted.vendorid=copybilltbl.vendorid )

begin
raiserror('Vendor ID is in Use in other table.',1,1)
rollback tran
end

触发copyvendors_del_up

delete from copyvendors
where vendorid=11

创建触发器

create trigger copybilltbl_ins_up
on
copybilltbl
after insert,update
as

if not exists ( select * from copyvendors where vendorid IN (select vendorid from inserted))
begin raiserror('Vendor is invalied',1,1)
rollback tran
end

触发copybilltbl_ins_up

insert into copybilltbl
values(12,'b32',3818,1500,2300)