SQL触发器
问题描述:
如果更新为tbl_repair_visit.TENANTSATISFACTION = 'Poor'
,我有附加的触发器。SQL触发器
的问题,如果我们改变了工程师命名工程师列更新,并且触发再次运行我有,如果TENANTSATISFACTION = 'Poor'
我如何设置这在所有只运行,如果TENANTSATISFACTION = 'Poor'
列被更新和ignor更新其他列
ALTER TRIGGER [dbo].[tr_CustomerSatisfactionAlertRepair]
ON [dbo].[tbl_repair_visit]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT alertmessagedata (TypeID, Contract, Address, ORDERID,
ENGINEERS, Sent, DateAdded)
SELECT '5', tbl_property.contract, tbl_property.fulladdress,
tbl_repair_visit.orderid, tbl_repair_visit.engineer,
0, GETDATE()
FROM TBL_REPAIR_VISIT
INNER JOIN
INSERTED X ON TBL_REPAIR_VISIT.VISITID = X.VISITID
INNER JOIN
TBL_PROPERTY ON TBL_REPAIR_VISIT.PROPREF = TBL_PROPERTY.PROPREF
WHERE tbl_repair_visit.TENANTSATISFACTION = 'Poor'
END
答
在触发更新,你可以检查一列正在更新:
IF UPDATE(TENANTSATISFACTION) BEGIN .... END
答
我不认为你可以指定。触发器UPDATE,DELETE和INSERT在表中执行UPDATE,DELETE或INSERT(可选)时触发。
您可以按照Mike K.的建议进行操作,并检查您感兴趣的列是否已更改。
答
你不能用嵌套的触发器选项做些什么吗?
(设置该选项关闭,以便触发器不会导致其他触发器被触发)
或者,也许你可以创建一个INSTEAD OF触发器,而不是AFTER触发器。 Offcourse,然后在你的INSTEAD OF触发器中,你还必须编写UPDATE(或insert)语句,该语句应该执行实际的更新或插入到附加逻辑旁边的表中。
答
如果连接表(即插入)具有空值,则表示要检测更改的字段已更改,则将旧版本的记录新版本左连接。在触发BL的恶果
create table family
(
id int not null identity(1,1),
name varchar(100) not null,
age int not null
);
insert into family(name,age) values('Michael', 32);
insert into family(name,age) values('Matthew', 23);
create trigger TrigUpdOnFamily on family
for update
as
if exists
(
select * from deleted
left join inserted on inserted.id = deleted.id
-- put the fields to detect here...
and inserted.age = deleted.age
-- ...detections
where inserted.age is null) begin
-- detect important fields
print 'age change';
end
else begin
-- ignore non-important fields
print 'nothing change';
end;
go
-- possible SqlCommand from .NET
update family set name = 'Michael', age = 20 where id = 1;
update family set name = 'Mateo', age = 23 where id = 2;
答
create table Programmer
(
id int not null identity(1,1),
name varchar(100) not null,
status varchar(20)
);
insert into Programmer(name,status) values('Pampers', 'Rich');
create trigger TrigUpdOnProgrammer on Programmer
for update
as
if exists
(
select * from deleted
left join inserted on inserted.id = deleted.id
-- put the fields to detect here...
and inserted.status = deleted.status -- detect if changed
-- ...detections
where inserted.status is null)
-- if changes detected on status, then check if it is Poor.
-- don't worry, there's no performance degradation. SQL AND is short-circuited
and exists(select * from inserted where status = 'Poor')
begin
print 'status changed';
end
else begin
print 'changes ignored';
end;
go
-- execute the following on succession, then check the output
update Programmer set status = 'Poor' where id = 1; -- changes detected
update Programmer set status = 'Poor' where id = 1; -- changes ignored
update Programmer set status = 'Rich' where id = 1; -- changes ignored
update Programmer set status = 'Poor' where id = 1; -- changes detected
很好的例子。 – dkretz 2009-02-03 12:29:49