求一批量更新的update触发器CREATE TABLE A(ID INT IDENTITY(1,1),A1 VARCHAR(10))
INSERT INTO A(A1)
SELECT '1' UNION ALL
SELECT '2' ...CREATE TABLE B(A1 VARCHAR(10),B1 VARCHAR(10))
INSERT INTO B(A1,B1)
SELECT '1','aaa' UNION ALL
SELECT '1','bbb' UNION ALL
SELECT '2','ccc' UNION ALL
SELECT '2','dddd'-- 如上两个表,当批量更新表A的A1字段时,自动更新表B的A1字段-- 最好给用游标和不用游标的两种实现方法-- 问题很简单,但我却不会,希望各位能帮个忙。
INSERT INTO A(A1)
SELECT '1' UNION ALL
SELECT '2' ...CREATE TABLE B(A1 VARCHAR(10),B1 VARCHAR(10))
INSERT INTO B(A1,B1)
SELECT '1','aaa' UNION ALL
SELECT '1','bbb' UNION ALL
SELECT '2','ccc' UNION ALL
SELECT '2','dddd'-- 如上两个表,当批量更新表A的A1字段时,自动更新表B的A1字段-- 最好给用游标和不用游标的两种实现方法-- 问题很简单,但我却不会,希望各位能帮个忙。
FOR update
AS
BEGIN
UPDATE B SET A1 = LO.A1
FROM B, UPDATED LO
.................
END
create trigger up_a on A
for update
as
update B set b.A1=a.A1
from inserted a,deleted c
where b.A1=c.A1 and a.b1=c.b1
go
这样在批量更新的时候不会出现“子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。” 这样的错误吗?
gocreate trigger trg_test on test for insert
as
begin
update a
set
Child =a.Child+1,
arrChildID=a.arrChildID+','+rtrim(b.ID)
from
test a,inserted b
where
charindex(',',b.ParentPath)>0
and
cast(a.ID as varchar)=reverse(left(reverse(b.ParentPath),charindex(',',reverse(b.ParentPath))-1))
end
goinsert into test select 1,'新闻中心',1,'0', 0,'1' --Child,arrChildID 让触发器自动维护
insert into test select 2,'图片中心',1,'0', 0,'2'
insert into test select 3,'软件中心',1,'0', 0,'3'
insert into test select 4,'国内新闻',1,'0,1',0,'4'
insert into test select 5,'娱乐新闻',1,'0,1',0,'5'
insert into test select 6,'体育新闻',1,'0,1',0,'6'
insert into test select 7,'热点新闻',1,'0,1',0,'7'
goselect * from test
/*
ID NAME NodeType ParentPath Child arrChildID
----------- ---------- ----------- ---------- ----------- --------------------
1 新闻中心 1 0 4 1,4,5,6,7
2 图片中心 1 0 0 2
3 软件中心 1 0 0 3
4 国内新闻 1 0,1 0 4
5 娱乐新闻 1 0,1 0 5
6 体育新闻 1 0,1 0 6
7 热点新闻 1 0,1 0 7
*/
godrop trigger trg_test
drop table test
go
go
CREATE TABLE A(ID INT IDENTITY(1,1),A1 VARCHAR(10))
INSERT INTO A(A1)
SELECT '1' UNION ALL
SELECT '2'
if object_id('[b]') is not null drop table [b]
go
CREATE TABLE B(A1 VARCHAR(10),B1 VARCHAR(10))
INSERT INTO B(A1,B1)
SELECT '1','aaa' UNION ALL
SELECT '1','bbb' UNION ALL
SELECT '2','ccc' UNION ALL
SELECT '2','dddd'create trigger up_a on A
for update
as
update B set b.A1=a.A1
from inserted a,deleted c
where a.id=c.id and c.a1=b.a1
goupdate a set a1=a1+1select * from b
/*
A1 B1
---------- ----------
2 aaa
2 bbb
3 ccc
3 dddd(4 行受影响)
*/
--TB1插入数据同时查数据到TB2
--=====================================================
create table TB1(name nvarchar(20),sex bit)create table TB2(name nvarchar(20),sex bit)insert TB1
select 'abc',1 union all
select '123',1 union all
select 'ABC',1Select * from tb1
/*
name sex
-------------------- -----
abc 1
123 1
ABC 1
*/
create trigger trg_test on TB1 for insert
as
begin
insert into TB2(name,sex)
select name,sex from inserted
end
goinsert TB1
select 'WWW',1Select * from tb1
/*
name sex
-------------------- -----
abc 1
123 1
ABC 1
WWW 1
*/
select * from tb2
/*
name sex
-------------------- -----
WWW 1
*/
还是报同样的错误,我有A、B、C、D、E等几个表,B、C、D、E等表中都有一字段可以和A中的一字段对应起来,没有外键约束。
我的触发器也是这样SQL codecreate trigger up_a on A
for update
as
update B set b.A1=a.A1
from inserted a,deleted c
where b.id=c.id and a.A1=c.A1;update C set C.A1=a.A1
from inserted a,deleted del
where c.id=del.id and a.A1=del.A1;
...
create trigger up_a on A
for update
as IF update(A1)
-- 我的触发器中有这样一句IF update(A1),我在执行批量更新的同时,检测到没有的数据我也把它insert into表中去了,这也触发了IF update(A1),但是这个触发器只是仅仅for update,然后才会报错?
-- 是不是这个原因? update B set b.A1=a.A1
from inserted a,deleted c
where b.id=c.id and a.A1=c.A1;update C set C.A1=a.A1
from inserted a,deleted del
where c.id=del.id and a.A1=del.A1;