日期:2014-05-18  浏览次数:20477 次

求一SQL 触发器
我有以下两表,

我想用触发器器控制:当我在操作table2的时候,在新增记录中如果qty<0.1且table1的type为b的时候要提示删除该行,该怎么写,请教各位,谢谢!



TABLE1:

ID TYPE MOD 

1 A 001
2 B 002
3 C 003

TABLE2:
ID QTY

1 2
2 3
3 4 




------解决方案--------------------
SQL code

go
reate trigger tri_tracy on table 
for insert
as
if exists(select 1 from(select a.*,a.TYPE,a.MOD 
from inserted a inner join table1 b )d where qty<0.1 and type='b')
begin
declare @str varchar(10)
select @str=ltrim(id) from(select a.*,a.TYPE,a.MOD 
from inserted a inner join table1 b )d where qty<0.1 and type='b'
print '请删除'+@str
end

------解决方案--------------------
SQL code

--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([ID] int,[TYPE] varchar(1),[MOD] varchar(3))
insert [table1]
select 1,'A','001' union all
select 2,'B','002' union all
select 3,'C','003'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([ID] int,[QTY] numeric(3,2))


go
if OBJECT_ID('tri_tracy') is not null
drop trigger tri_tracy
go
create trigger tri_tracy on table2
for insert
as
if exists(select 1 from(select a.*,b.[TYPE],b.[MOD]
from inserted a inner join table1 b on a.id=b.id)d where qty<0.1 and [type]='b')
begin
declare @str varchar(10)
select @str=ltrim(id) from(select a.*,b.[TYPE],b.[MOD]
from inserted a inner join table1 b on a.id=b.id)d where qty<0.1 and [type]='b'
print '请删除'+@str
end

insert [table2]
select 1,0.09 union all
select 2,0.09 union all
select 3,0.4


/*
请删除2
(3 行受影响)
*/