示例3.sql

SQL基础教程上的一些源码

CREATE DATABASE testTrigger
GO

USE testTrigger
GO

CREATE TABLE BOOKS  --书表
(	
	BOOKid   int  not null ,  --本书编号
	BOOKName varchar(100) not null, --书名
	BOOKNum  int default 0 not null --库存量
)  
                                                                                                       
CREATE TABLE BOOKRecord --出入库记录表
(
	id  int  identity(1,1) not null,  --记录流水号为自增字段
	BOOKid  int not null,  --本书编号
	Quantity int  default 0 ,  --
	Date  datetime default getdate(), --出入库日期
	Operate int default 0 --0为入库，为出库
) 
GO

USE testTrigger
GO
INSERT INTO BOOKRecord (BOOKid,Quantity,Operate) 
values (0,100,0)
IF Exists (select * from BOOKS WHERE BOOKid =0)
	UPDATE BOOKS SET BOOKNUM=BOOKNum+100 WHERE BOOKID=100
ELSE
	INSERT INTO BOOKS (BOOKid,BOOKName,BOOKNum)
	values (0,'《SQL Server 2005学习入门》',100)

SELECT * FROM BOOKRecord
GO
SELECT * FROM BOOKS
GO

CREATE TRIGGER trBOOKRecord ON BOOKRecord  
for INSERT  
as
DECLARE @BookID  int
DECLARE @Quantity  int
DECLARE @Operate   int
IF @@ROWCOUNT >0
BEGIN
SELECT @BookID = BOOKid, @Quantity = Quantity, @Operate = Operate FROM Inserted

IF @Operate = 0
	UPDATE BOOKS SET BOOKNum = BOOKNum + @Quantity WHERE BOOKid = @BookID
ELSE
	UPDATE BOOKS SET BOOKNum = BOOKNum - @Quantity WHERE BOOKid  = @BookID
END

USE testTrigger
GO

INSERT INTO BOOKRecord (BookID, Quantity,Operate)
VALUES (0,50,1)
GO

SELECT * FROM BOOKRecord
GO
SELECT * FROM BOOKS
GO