假设有A,B两个字段是用来比较的: create trigger twocolumnupdate on tb after update as begin if exists(select 1 from inserted t1,deleted t2 where (t1.A=t2.A and t1.B!=t2.b) or (t1.A!=t2.A and t1.B=t2.b)) 此处是要执行的内容...
return? 不是禁用触发器吗 两个同时修改的时候不触发 就and吗
create table tb ( ID int identity(1,1), A varchar(10), B varchar(10), C varchar(10) )insert into tb select 'a1','b1','c1' union all select 'a2','b2','c2' create trigger tb_twocolumnupdate on tb after update as begin if update(A) or update(B) rollback end update tb set A='1111' where ID=1事务在触发器中结束。批处理已中止。
create table tb ( ID int identity(1,1), A varchar(10), B varchar(10), C varchar(10) )insert into tb select 'a1','b1','c1' union all select 'a2','b2','c2' create trigger tb_twocolumnupdate on tb after update as begin if update(A) and update(B) --AND 或者 OR 看你的需求 rollback endupdate tb set A='1111' where ID=1 (1 行受影响)update tb set A='1111',B='2222' where ID=1事务在触发器中结束。批处理已中止。
你不是只要改其中一个不触发,用or ,两个同时修改的时候不触发 就and 放在触发器第一行 CREATE TRIGGER tupdate_tb ON tb FOR update AS begin --下面这条语句,指定如有更新a或者b字段,直接退出触发器,即后面的正常处理代码不执行 if update(a) or update (b) return --下面是正常的处理 -- end
if 还要跟else 吗 还是不用 我有两个if啊 一个判断是否执行触发器 一个判断是增加还是修改 都不用else吗
create trigger tb_twocolumnupdate on 销售表 after update as begin if update(A) and update(B) --当销售表中的两个字段同时修改时,不更新价格表 begin rollback end else begin update 价格表.... end end
销售表sales,主键:id,与价格表关联:item_code 价格表prices--插入触发器 create trigger tinsert_sales on sales for insert as begin if exists(select 1 from prices where prices.item_code = inserted.item_code) begin --存在时更新 update prices set price = i.price from inserted where prices.item_code = inserted.item_code end else begin insert into prices(item_code,price) select i.price,i.item_code from inserted i where not exists(select 1 from prices where prices.item_code = i.item_code) end end--更新触发器 create trigger tupdate_sales on sales for update as begin --更新指定的字段不执行 if update(a) and update(b) and exists(select 1 from inserted i,deleted d where i.id = d.id and i.a <> d.a and i.b <> d.b) begin return end --更改价格,更新价格表 if update(price) and exists(select 1 from prices where prices.item_code = inserted.item_code) begin --存在时更新 update prices set price = i.price from inserted i,deleted d where prices.item_code = i.item_code and i.id = d.id and i.price <> d.price end else begin insert into prices(item_code,price) select i.price,i.item_code from inserted i where not exists(select 1 from prices where prices.item_code = i.item_code) end end
更新的有点问题,更正一下 --更新触发器 create trigger tupdate_sales on sales for update as begin --更新指定的字段不执行 if update(a) and update(b) and exists(select 1 from inserted i,deleted d where i.id = d.id and i.a <> d.a and i.b <> d.b) begin return end --更改价格,更新价格表 if update(price) and exists(select 1 from prices,inserted i,deleted d where i.id = d.id and prices.item_code = i.item_code ) begin --存在时更新 update prices set price = i.price from inserted i,deleted d where prices.item_code = i.item_code and i.id = d.id and i.price <> d.price end else begin insert into prices(item_code,price) select i.price,i.item_code from inserted i where not exists(select 1 from prices where prices.item_code = i.item_code) end end
if update(a) or update (b) return
create trigger twocolumnupdate
on tb
after update
as
begin
if exists(select 1 from inserted t1,deleted t2 where (t1.A=t2.A and t1.B!=t2.b) or (t1.A!=t2.A and t1.B=t2.b))
此处是要执行的内容...
两个同时修改的时候不触发 就and吗
create table tb
(
ID int identity(1,1),
A varchar(10),
B varchar(10),
C varchar(10)
)insert into tb
select 'a1','b1','c1' union all
select 'a2','b2','c2'
create trigger tb_twocolumnupdate
on tb
after update
as
begin if update(A) or update(B)
rollback
end
update tb set A='1111' where ID=1事务在触发器中结束。批处理已中止。
create table tb
(
ID int identity(1,1),
A varchar(10),
B varchar(10),
C varchar(10)
)insert into tb
select 'a1','b1','c1' union all
select 'a2','b2','c2'
create trigger tb_twocolumnupdate
on tb
after update
as
begin if update(A) and update(B) --AND 或者 OR 看你的需求
rollback
endupdate tb set A='1111' where ID=1
(1 行受影响)update tb set A='1111',B='2222' where ID=1事务在触发器中结束。批处理已中止。
放在触发器第一行
CREATE TRIGGER tupdate_tb ON tb
FOR update
AS
begin
--下面这条语句,指定如有更新a或者b字段,直接退出触发器,即后面的正常处理代码不执行
if update(a) or update (b) return
--下面是正常的处理
--
end
我有两个if啊 一个判断是否执行触发器 一个判断是增加还是修改 都不用else吗
on 销售表
after update
as
begin if update(A) and update(B) --当销售表中的两个字段同时修改时,不更新价格表
begin
rollback
end
else
begin
update 价格表....
end
end
我的触发器是 销售表增加修改时 更新价格表 如果有就修改 没有就新增,
但是 销售表下推销售发票时销售表也会更新,这里有2个字段更新,
我想这两个字段同时修改时 触发器不执行
然后 按那个if update()and if update()
执行后 增加销售表 价格表增加
就是这样了
销售表sales,主键:id,与价格表关联:item_code
价格表prices--插入触发器
create trigger tinsert_sales on sales
for insert
as
begin
if exists(select 1 from prices where prices.item_code = inserted.item_code)
begin
--存在时更新
update prices
set price = i.price
from inserted
where prices.item_code = inserted.item_code
end
else
begin
insert into prices(item_code,price)
select i.price,i.item_code
from inserted i
where not exists(select 1 from prices where prices.item_code = i.item_code)
end
end--更新触发器
create trigger tupdate_sales on sales
for update
as
begin
--更新指定的字段不执行
if update(a) and update(b)
and exists(select 1 from inserted i,deleted d where i.id = d.id
and i.a <> d.a
and i.b <> d.b)
begin
return
end --更改价格,更新价格表
if update(price) and exists(select 1 from prices where prices.item_code = inserted.item_code)
begin
--存在时更新
update prices
set price = i.price
from inserted i,deleted d
where prices.item_code = i.item_code
and i.id = d.id
and i.price <> d.price
end
else
begin
insert into prices(item_code,price)
select i.price,i.item_code
from inserted i
where not exists(select 1 from prices where prices.item_code = i.item_code)
end
end
--更新触发器
create trigger tupdate_sales on sales
for update
as
begin
--更新指定的字段不执行
if update(a) and update(b)
and exists(select 1 from inserted i,deleted d where i.id = d.id
and i.a <> d.a
and i.b <> d.b)
begin
return
end --更改价格,更新价格表
if update(price) and exists(select 1 from prices,inserted i,deleted d
where i.id = d.id
and prices.item_code = i.item_code
)
begin
--存在时更新
update prices
set price = i.price
from inserted i,deleted d
where prices.item_code = i.item_code
and i.id = d.id
and i.price <> d.price
end
else
begin
insert into prices(item_code,price)
select i.price,i.item_code
from inserted i
where not exists(select 1 from prices where prices.item_code = i.item_code)
end
end