create trigger tri_1 on tab_accept for update,delete
as
if exists(select * from inserted)
begin
update tab_career set IDcard=inserted.IDcard from tab_career,inserted where 条件(注即表tab_accept和tab_career相关联的条件)
end
else
begin
delete tab_career where idcard in (select idcard from deleted)
end这个例子中if exists(select * from inserted) 是什么意思?谁能逐句解释一下?谢谢!
as
if exists(select * from inserted)
begin
update tab_career set IDcard=inserted.IDcard from tab_career,inserted where 条件(注即表tab_accept和tab_career相关联的条件)
end
else
begin
delete tab_career where idcard in (select idcard from deleted)
end这个例子中if exists(select * from inserted) 是什么意思?谁能逐句解释一下?谢谢!
http://community.csdn.net/Expert/topic/4099/4099883.xml?temp=.2569544
改一下就是了,难道非得要最终源码?
on A
for insert,update,delete
AS
BEGIN
if exists(select 1 from inserted)
begin
if exists(select 1 from deleted)
begin
update B
set
仓库 = C.进货仓,
货品编号 = C.货品编号,
计量单位 = C.计量单位,
出入库数量= C.入库数量,
单价 = C.单价,
金额 = C.金额
from
B,inserted C
where
B.单号 = C.产品入库单号
end
else
begin
insert into B(单号,仓库,货品编号,计量单位,出入库数量,单价,金额)
select 产品入库单号,进货仓,货品编号,计量单位,入库数量,单价,金额 from inserted
end
end
else
begin
delete from B where 单号 = (select 产品入库单号 from deleted)
end
END
GO
我想要SQL SERVER 2000 的源码,针对这个例子的代码,我现在有些地方还比较模糊
谢谢!=====================================
贴源码的每人20分,超过5个另外开帖子
--新增的
Create Trigger InsertB On A
FOR INSERT
As
Insert B Select 产品入库单号,进货仓,货品编号,计量单位,入库数量,单价,金额 from Inserted
GO--修改的
Create Trigger UpdateB On A
FOR UPDATE
As
Update B Set 仓库=A.进货仓,货品编号=A.货品编号,计量单位=A.计量单位,出入库数量=A.入库数量,单价=A.单价,金额=A.金额 from B Inner Join Inserted A On A.产品入库单号=B.单号
GO--删除的
Create Trigger DeleteB On A
FOR DELETE
As
Delete B From B Inner Join Deleted A On B.单号=A.产品入库单号
GO
create trigger trg_1
on A
for insert,update,delete
AS
BEGIN
if exists(select 1 from inserted) ^^^^这一行是否可以具体解释一下
begin
if exists(select 1 from deleted) ^^^^这一行是否可以具体解释一下
begin
update B ^^^^这一行是否可以具体解释一下
set
仓库 = C.进货仓,
货品编号 = C.货品编号,
计量单位 = C.计量单位,
出入库数量= C.入库数量,
单价 = C.单价,
金额 = C.金额
from
B,inserted C ^^^^这一行是否可以具体解释一下
where
B.单号 = C.产品入库单号
end
else
begin
insert into B(单号,仓库,货品编号,计量单位,出入库数量,单价,金额)
select 产品入库单号,进货仓,货品编号,计量单位,入库数量,单价,金额 from inserted
end
end
else
begin
delete from B where 单号 = (select 产品入库单号 from deleted)
end
END
GO以上内容基本可以理解,是否可以请你再具体解释一下?
Create Trigger InsertB On A
FOR INSERT
As
Insert B Select 产品入库单号,进货仓,货品编号,计量单位,入库数量,单价,金额 from Inserted ^^^^这一行是否可以具体解释一下?
GO
上面一行是否可以请你解释一下?=====================================
贴源码的每人20分,达不到5个人就平分,超过5个另外开帖子,谢谢关注!
=====================================
贴源码的每人20分,达不到5个人就平分,超过5个另外开帖子,谢谢关注!
on a
for insert,update,delete
as
IF exists (select * from inserted) and not exists (select * from deleted)
begin
insert b select (select 产品入库单号 from inserted),(select 进货仓 from inserted),
(select 货品编号 from inserted),(select 计量单位 from inserted),(select 入库数量 from inserted),
(select 单价 from inserted),(select 金额 from inserted)
end
if exists(select * from inserted ) and exists (select * from deleted)
begin
update b set 单号=产品入库单号,仓库=进货仓,b.货品编号=inserted.货品编号,b.计量单位=inserted.计量单位,
出入库数量=入库数量,b.单价=inserted.单价,b.金额=inserted.金额 from b join inserted on 单号=产品入库单号
end
IF not exists (select * from inserted) and exists (select * from deleted)
begin
delete from b where b.单号 in (select 产品入库单号 from deleted )
end
这时就要判断是哪一个动作触发了该触发器。才能正确地执行触发器后的语句。具体解释一下:
--IF exists (select * from inserted) and not exists (select * from deleted)
如果当向a表中发生insert 动作即插入数据时,系统只会自动生成inseted表,而不会同时生成deleted表
--if exists(select * from inserted ) and exists (select * from deleted)
如果当向a表中发生update动作即修改数据时,系统会自动生成inseted表,而同时生成deleted表
--IF not exists (select * from inserted) and exists (select * from deleted)
如果当向a表中发生delete动作即删除记录时,系统只会自动生成deleted表,而不会同时生成inserted表
Create Trigger UpdateB On A
FOR UPDATE
As
Update B Set 仓库=A.进货仓,货品编号=A.货品编号,计量单位=A.计量单位,出入库数量=A.入库数量,单价=A.单价,金额=A.金额 from B Inner Join Inserted A On A.产品入库单号=B.单号
GO这段代码中如果在最后再加上几个条件发现就没有用,比如再加上:B.仓库=A.进货仓,
只有 A.产品入库单号=B.单号 这个条件有用,不知为什么?能否解答?
begin
insert b select (select 产品入库单号 from inserted),(select 进货仓 from inserted),
(select 货品编号 from inserted),(select 计量单位 from inserted),(select 入库数量 from inserted),
(select 单价 from inserted),(select 金额 from inserted)
end这段代码是完全错误的。你往表中同时插入多条数据看看,肯定会报错。