求一SQL 触发器

[zts107]

我有以下两表,

我想用触发器器控制：当我在操作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

[开着拖拉机泡妞]

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

[开着拖拉机泡妞]

--> 测试数据：[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 行受影响)
*/

[zts107]

谢谢楼上的，我明天测试一下。