|
aku1 发表于 2008-6-4 18:51:08 |
USE TEST
GO
if object_id('tb3') is not null
drop table tb3
go
create table tb3(code int identity(1,1), name nvarchar(12),Pay money)
go
create trigger trig1 on tb3
after insert
AS
if (select count(code) from inserted where Pay<1500)>0
begin
RAISERROR('THE DATA INTEGRITY PROBLEM',16,1)
ROLLBACK TRANSACTION
END;
GO
insert tb3 (name,pay) values('Tom Xiang',99)
insert tb3 (name,pay) values('Eddie Wei',5000)
go
insert tb3 (name,pay) values('Jimmy Zhang',10000)
go
if object_id('产品数据') is not null
drop table 产品数据
go
create table 产品数据(产品编号 int primary key,产品 Char(6),已订购量 int)
go
insert 产品数据 select 40,'a',100
union all select 75,'b', 200
union all select 80,'c' ,300
union all select 100,'d' ,500
if object_id('订货明细') is not null
drop table 订货明细
go
create table 订货明细(定单号码 int,产品编号 int,单价 money,数量 float(6),折扣 Real
constraint PK_1 foreign key( 产品编号) references 产品数据(产品编号) )
go
create trigger trig4 on 订货明细
after insert
As
if @@rowcount=1
begin
update 产品数据
set 产品数据.已订购量=产品数据.已订购量+inserted.数量
from inserted
Where 产品数据.产品编号=inserted.产品编号
end
else
Begin
update 产品数据
set 产品数据.已订购量=产品数据.已订购量+(select sum(数量)from inserted where 产品数据.产品编号=inserted.产品编号)
where 产品数据.产品编号 in (select 产品编号 from inserted)
end
GO
Declare @T table (order_id int not null,proc_id int not null,price money not null,QTY smallint not null,discount real not null)
insert @t select 11033,75,20.00,10,0
union all select 11022,75,20.00,21,0
union all select 11011,75,20.00,14,0
union all select 11033,40,18.40,10,0
union all select 11022,40,18.40,10,0
insert 订货明细 select * from @t
go
insert 订货明细(定单号码,产品编号,单价,数量,折扣) values(11011,80,20.00,5,0)
| 
