参考 CREATE TRIGGER 名3 ON dbo.tabMain FOR delete AS delete db2..tabSlave where 编号 in (select 编号 from deleted)
create trigger t_name on tableName for delete as update table_other set col1=a.col1,col2=a.col2,... from deleted a join table_other b on a.id=b.id ordelete from table_other where id in (select id from deleted)
create trigger t_name on tableName for delete as update table_other set col1=a.col1,col2=a.col2,... from deleted a join table_other b on a.id=b.iddelete from table_other where id in (select id from deleted)
我的代码是: CREATE TRIGGER in_dtl_delete ON in_dtl FOR DELETE AS declare @s_mtrl_cd varchar(12), @qty_nr numeric(12,2), @s_qty_nr numeric(12,2), @unitprice_cy numeric(12,3)select @s_mtrl_cd=s_mtrl_cd,@qty_nr=qty_nr,@unitprice_cy=unitprice_cy, @s_qty_nr=s_qty_nr from deletedupdate left_tx set qty_nr = qty_nr - @s_qty_nr, amt_cy = amt_cy - @qty_nr * @unitprice_cy where mtrl_cd = @s_mtrl_cd 说明:当删除in_dtl表中的记录时触发,修改left_tx中的字段 问题是:当我在in_dtl中删除一批记录时,left_tx中只修改相应的一条记录????
--改成下面的代码,简单又能实现你的目的 CREATE TRIGGER in_dtl_delete ON in_dtl FOR DELETE AS update A set A.qty_nr = A.qty_nr - B.s_qty_nr, A.amt_cy = A.amt_cy - B.qty_nr * B.unitprice_cy FROM left_tx A inner join deleted B on A.mtrl_cd = B.s_mtrl_cd
你的代码本身写的就是删除一条记录时候能用,一批记录就不适用 select @s_mtrl_cd=s_mtrl_cd,@qty_nr=qty_nr,@unitprice_cy=unitprice_cy, @s_qty_nr=s_qty_nr from deleted 你看上面的语句,如果deleted是多条记录,这些变量只能保存一个值,所以当然只有一条更新了
大家来帮帮我。。 为什么没有达到我要的效果呢,我记录要ORACLE 中是有一个叫for each row 参数,在sql server 中有没有象这样的啊????
参考
CREATE TRIGGER 名3 ON dbo.tabMain
FOR delete
AS
delete db2..tabSlave where 编号 in (select 编号 from deleted)
for delete
as
update table_other set col1=a.col1,col2=a.col2,... from deleted a join table_other b on a.id=b.id
ordelete from table_other where id in (select id from deleted)
for delete
as
update table_other set col1=a.col1,col2=a.col2,... from deleted a join table_other b on a.id=b.iddelete from table_other where id in (select id from deleted)
CREATE TRIGGER in_dtl_delete ON in_dtl
FOR DELETE
AS
declare
@s_mtrl_cd varchar(12),
@qty_nr numeric(12,2),
@s_qty_nr numeric(12,2),
@unitprice_cy numeric(12,3)select @s_mtrl_cd=s_mtrl_cd,@qty_nr=qty_nr,@unitprice_cy=unitprice_cy, @s_qty_nr=s_qty_nr
from deletedupdate left_tx
set qty_nr = qty_nr - @s_qty_nr,
amt_cy = amt_cy - @qty_nr * @unitprice_cy
where mtrl_cd = @s_mtrl_cd 说明:当删除in_dtl表中的记录时触发,修改left_tx中的字段
问题是:当我在in_dtl中删除一批记录时,left_tx中只修改相应的一条记录????
CREATE TRIGGER in_dtl_delete ON in_dtl
FOR DELETE
AS
update A
set A.qty_nr = A.qty_nr - B.s_qty_nr,
A.amt_cy = A.amt_cy - B.qty_nr * B.unitprice_cy
FROM left_tx A inner join deleted B on A.mtrl_cd = B.s_mtrl_cd
select @s_mtrl_cd=s_mtrl_cd,@qty_nr=qty_nr,@unitprice_cy=unitprice_cy, @s_qty_nr=s_qty_nr
from deleted
你看上面的语句,如果deleted是多条记录,这些变量只能保存一个值,所以当然只有一条更新了
为什么没有达到我要的效果呢,我记录要ORACLE 中是有一个叫for each row 参数,在sql server 中有没有象这样的啊????
不用SQL Server的精练之处(批量更新),还去用一条一条循环更新
好的、巧的方法要学!
FOR INSERT
AS
declare
@s_mtrl_cd varchar(12),
@s_qty_nr numeric(12,2),
@qty_nr numeric(12,2),
@unitprice_cy numeric(12,3),
@lt_mtrl_cd varchar(12)select @s_mtrl_cd=s_mtrl_cd,@qty_nr=qty_nr,@unitprice_cy=unitprice_cy, @s_qty_nr=s_qty_nr
from insertedif exists(select mtrl_cd from left_tx where mtrl_cd = @s_mtrl_cd)
update left_tx
set qty_nr = qty_nr + @s_qty_nr,
amt_cy = amt_cy + @qty_nr * @unitprice_cy
where mtrl_cd = @s_mtrl_cd
else
insert into left_tx
values(@s_mtrl_cd,@s_qty_nr,@qty_nr*@unitprice_cy,'MED')当我插入一批的时候,left_tx 中会有一批的变化,而不象上面的只触发一条记录??
请教了????