表tableA 的列ColA内容为
aaa
bbb
ccc
ddd
eee
……表tableB的列ColB内容为
aaa
aaa,bbb,ccc
bbb,eee
……怎么写一个触发器,在删除tableA内容的时候判断是否已被tableB使用
aaa
bbb
ccc
ddd
eee
……表tableB的列ColB内容为
aaa
aaa,bbb,ccc
bbb,eee
……怎么写一个触发器,在删除tableA内容的时候判断是否已被tableB使用
---------------------------------------------
怎么看不懂你说什么,触发器好不是用来做判断用的。是如果已被tableB所用就不删除,还是同时删除tableB中相应的值(或删除记录)?
---------------------------------------------
怎么看不懂你说什么,触发器好像不是用来做判断用的。是如果已被tableB所用就不删除,还是同时删除tableB中相应的值(或删除记录)?上面打掉个字
create table tablea (cola varchar(10))
create table tableb (colb varchar(50))
insert tablea
select 'aaa' union all
select 'bbb' union all
select 'ccc' union all
select 'ddd' union all
select 'eee'
insert tableb
select 'aaa' union all
select 'aaa,bbb,ccc' union all
select 'bbb,eee'
select * from tablea
select * from tableb
go
----为tablea创建删除触发器
create trigger tritmp on tablea
for delete
as
begin transaction
declare @count int
select @count = count(1) from deleted x where exists(select 1 from tableb y where charindex(x.cola,y.colb) > 0)
if @count > 0 /*符合楼主的要求时*/
begin
raiserror('Sorry,Can''t delete',16,1) /*楼主自己的处理代码*/
rollback /*回滚,使删除无效*/
return
end
commit transaction /*保存删除*/
go
----测试删除触发器
delete from tablea where cola = 'aaa'
go
----查看是否被删除
select * from tablea
----清除测试表
drop table tablea
drop table tableb
insert into t1 values('aaa')
insert into t1 values('bbb')
insert into t1 values('ccc')
insert into t1 values('ddd')
insert into t1 values('eee')create table t2(col2 varchar(50))
insert into t2 values('aaa')
insert into t2 values('aaa,bbb,ccc')
insert into t2 values('bbb,eee')alter trigger t1_d on t1
INSTEAD OF delete
as
declare @tt char(10)
select @tt=coa1 from deleted
begin
if exists(select * from t2 where col2 like '%'+ rtrim(@tt) +'%')
raiserror('数据正在被使用',16,1)
else
delete from t1 where coa1 =@tt
end
是你太蠢了。别人都能知道我在说什么,快点醒悟吧