22,207
社区成员
发帖
与我相关
我的任务
分享
1 if not exists (select 1 from sysobjects where id = object_id(N'tr_insert') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
create trigger tr_insert
2 if exists (select 1 from sysobjects where id = object_id(N'tr_insert') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger tr_insert
create trigger tr_insert
on table
for insert
as
raiserror('insert stop.', 16, 1)
go
--2
DROP TRIGGER 'tr_insert'
create table test(ID int,NAME varchar(10),NodeType int,ParentPath varchar(10),Child int,arrChildID varchar(20))
go
create 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
go
insert 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'
go
select * 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
*/
go
drop trigger trg_test
drop table test
go