表childdepart:
-----------------------------------------------------------
ID parentid childname upfilekind childid
23 17 22 0 1
24 17 22 0 2表departs
-----------------------------------------------------------
ID depart type upfilekind parentid
17 00 0 000 0 表childdepart的parentid对应departs的ID要求:当我往childdepart表中插入一条记录时.
INSERT INTO childdepart (parentid, childname, upfilekind, childid)VALUES (17, '22', '0', '0')上面语句中parentid=17,这个时候判断 "departs" 表中ID=17的记录的parentid字段的值,如果这个记录中的parentid的值为0,则要更新它为1,为1时不变化. 触发器代码:CREATE trigger uppid on childdepart
for update
as
declare @pid int,@parentid int
select @pid=parentid from inserted
select @parentid=parentid from departs
if exists(select ID from inserted) and @parentid=0
update departs set parentid=@pid where ID=@pid
我插入一条新记录
INSERT INTO childdepart (parentid, childname, upfilekind, childid)VALUES (17, '22', '0', '0')
对应的departs表中ID=17的parentid为0,但是触发器好像没有执行,没有将 "0" 更新为 "1"
CREATE trigger uppid on childdepart
for update
as
declare @pid int,@parentid int
select @pid=parentid from inserted
select @parentid=parentid from departs where ID=@pid
if exists(select ID from inserted) and @parentid=0
update departs set parentid=@pid where ID=@pid
for update
as
declare @pid int,@parentid int
select @pid=parentid from inserted
select @parentid=parentid from departs where id = @pid
if exists(select ID from inserted) and @parentid=0
update departs set parentid=@pid where ID=@pid
请参考
http://www.fawcette.com/china/XmlFile.aspx?ID=268
for insert --插入是触发,应该用insert
as
update departs set parentid=1
from departs a join(
select parentid from inserted group by parentid
)b on a.id=b.parentid
where a.parentid=0
for update
as
beginupdate A
set A.parentid=B.parentid
from departs A
inner join inserted B on B.parentid=A.ID
where A.parentid=0end
departs.parentid=0 且 inserted的parentid要等於departs的ID
create table childdepart(ID int,parentid int,childname int,upfilekind int,childid int)
insert childdepart
select 23,17,22,0,1
union all select 24,17,22,0,2create table departs(ID int,depart char(2),type char(2),upfilekind char(3),parentid int)
insert departs select 17,'00',0,'000',0
go--触发器
create trigger uppid on childdepart
for insert --插入是触发,应该用insert
as
update departs set parentid=1
from departs a join(
select parentid from inserted group by parentid
)b on a.id=b.parentid
where a.parentid=0
go--插入数据测试
INSERT INTO childdepart (parentid, childname, upfilekind, childid)VALUES (17, '22', '0', '0')
go--显示触发器的效果
select * from departs
go--删除测试
drop table departs,childdepart/*--测试结果
ID depart type upfilekind parentid
----------- ------ ---- ---------- -----------
17 00 0 000 1(所影响的行数为 1 行)
--*/