示例
1、insert
表bai1 (ID,NAME)
表2 (ID,NAME)
当用户插入表1数据后du,表2也被插入相同zhi的数据
CREATE TRIGGER TRI1
ON 表1
FOR INSERT
AS
BEGIN
INSERT INTO 表2 SELECT * FROM INSERTED
END
GO
2、delete
表1 (ID,NAME)
表2 (ID,AGE,GENDER)
当用户删除表1某条baiID的数据后,表2相应ID的数据也被删除
CREATE TRIGGER TRI2
ON 表1
FOR DELETE
AS
BEGIN
DECLARE @id INT
SELECT @id FROM DELETED
DELETE 表2
WHERE ID = @id
END
GO
3、update
表1 (ID,NAME)
表2 (ID,NAME)
当用户更改表1 NAME列数据后,表2相应ID的数据也同时更新
CREATE TRIGGER TRI3
ON 表1
FOR UPDATE
AS
IF UPDATE(NAME)
BEGIN
UPDATE 表2
SET A.NAME = B.NAME
FROM 表2 A, INSERTED B
WHERE A.ID = B.ID
END
GO
1、K3界面新增采购订单,使用触发器把所有物料的采购数量改为原来的十倍。
create trigger TRI1
on POOrderEntry
after insert
as
begin
update POOrderEntry
set FAuxQty=FAuxQty*2
where exists (
select * from inserted where POOrderEntry.FInterID=inserted.FInterID
)
end
go
2、K3界面修改采购订单的备注信息为:测试日期触发器,新增一个触发器为,当采购订单表头 备注 列 修改后把表头日期加十天。
create trigger TRI2
on POOrder
for INSERT
as
if 1=((select 1 from inserted where fexplanation='测试日期触发器'))
begin
declare @date datetime
select @date = dateadd(day,10,Fdate)
from inserted
begin
update POOrder
set Fdate=@date
where
FInterID = (select FInterID from inserted)
end
end
go
3、K3中 采购订单 每天做的第一张单子不允许删除
create trigger TRI3
on POOrder
instead of delete
as
begin
declare @id int,
@date datetime,
@minidate datetime,
@miniid int
select @id=FInterID from POOrder where FInterID=(select FInterID from deleted)
select @date=FDate from POOrder where FInterID=(select FInterID from deleted)
--select @id = FInterID from deleted
--select @date = Fdate from deleted
select @miniid =(select top 1 FInterID from POOrder where FDate=@date order by FInterID)
if @miniid=@id
begin
raiserror('每天做的第一张单子不允许删除',16,1)
rollback tran
end
end
go