--创建测试表
create table table1(
stkID varchar(20) null,
productID varchar(20) null,
ProductName varchar(20) null,
Qty int null,
CQty int null,
UintPrice decimal null
)
create table table2(
IstkID varchar(20) null,
StkID int null,
ProductID varchar(20) null,
ProductName varchar(20) null,
Qty int null,
UintPrice decimal null
)
go
--放入测试数据
insert into table1 values('051123001','P0001','电脑',8,5,4500)
insert into table2 values('I05110001','051123001','P0001','电脑',3,4500)
--创建表2的delete触发器
create TRIGGER rrr
on table2 for delete as
update table1 set CQty=a.Qty from table1 a,deleted b where a.StkID=b.StkID and a.ProductID=b.ProductID
go
--创建表1更新update触发器
create TRIGGER ttt
on table1 for update as
update table2 set Qty=(a.Qty-a.CQty) from table1 a,table2 b where a.StkID=b.StkID and a.ProductID=b.ProductID--删除测试数据
drop TRIGGER rrr
drop TRIGGER ttt
drop table table1
drop table table2
create table table1(
stkID varchar(20) null,
productID varchar(20) null,
ProductName varchar(20) null,
Qty int null,
CQty int null,
UintPrice decimal null
)
create table table2(
IstkID varchar(20) null,
StkID int null,
ProductID varchar(20) null,
ProductName varchar(20) null,
Qty int null,
UintPrice decimal null
)
go
--放入测试数据
insert into table1 values('051123001','P0001','电脑',8,5,4500)
insert into table2 values('I05110001','051123001','P0001','电脑',3,4500)
--创建表2的delete触发器
create TRIGGER rrr
on table2 for delete as
update table1 set CQty=a.Qty from table1 a,deleted b where a.StkID=b.StkID and a.ProductID=b.ProductID
go
--创建表1更新update触发器
create TRIGGER ttt
on table1 for update as
update table2 set Qty=(a.Qty-a.CQty) from table1 a,table2 b where a.StkID=b.StkID and a.ProductID=b.ProductID--删除测试数据
drop TRIGGER rrr
drop TRIGGER ttt
drop table table1
drop table table2
--创建表2的insert触发器
create trigger tr_table2_insert
on table2
for insert
as
update table1 set cqty=a.cqty-b.qty from table1 a,inserted b
where a.StkID=b.StkID and a.ProductID=b.ProductID
----測試:
--增加入庫量:
insert into table2 values('I05110002','051123001','P0001','電腦',1,4500)select * from table1
select * from table2
結果:
stkID productID ProductName Qty CQty UintPrice
-------------------------------- ------ ----------- --------------------
051123001 P0001 電腦 8 4 4500IstkID StkID ProductID ProductName Qty UintPrice
-------------------- ----------- -------------------- --------------------
I05110001 51123001 P0001 電腦 3 4500
I05110002 51123001 P0001 電腦 1 4500
發現Cqty少了一個--创建表2的delete触发器
create trigger tr_table2_delete
on table2
for delete
as
update table1 set cqty=a.cqty+b.qty from table1 a,deleted b
where a.StkID=b.StkID and a.ProductID=b.ProductID
--
delete from table2 where istkid='I05110002'
select * from table1
select * from table2
結果:stkID productID ProductName Qty CQty UintPrice
-------------------- -------------------- -------
051123001 P0001 電腦 8 5 4500
IstkID StkID ProductID ProductName Qty UintPrice
-------------------- ----------- ---------------
I05110001 51123001 P0001 電腦 3 4500發現Cqty多了一個