示例5.sql

来自「SQL基础教程上的一些源码」· SQL 代码 · 共 50 行

INSERT INTO BOOKS (BOOKid,BOOKName,BOOKNum)
values (0,'《SQL Server 2005学习入门》',0)
GO

INSERT INTO BOOKRecord (BOOKid,Quantity,Operate) 
values (0,100,0)
GO
	
SELECT * FROM BOOKS
SELECT * FROM BOOKRecord
GO

INSERT INTO BOOKRecord (BOOKid,Quantity,Operate) 
	values (0,100,0)
GO
	
SELECT * FROM BOOKS
SELECT * FROM BOOKRecord
GO

CREATE TRIGGER trFOUPDATE ON BOOKRecord  
for UPDATE  
as
DECLARE @BookID  int
DECLARE @Quantity  int
DECLARE @Operate   int

DECLARE @b int
DECLARE @q int

IF @@ROWCOUNT >0
BEGIN
SELECT @BookID = BOOKid, @Quantity = Quantity, @Operate = Operate FROM deleted
SELECT @b =Bookid ,@q =Quantity FROM inserted WHERE BOOKid =@BookID
IF @Operate = 0
UPDATE BOOKS SET BOOKNum = BOOKNum - @Quantity +@q WHERE BOOKid = @BookID

ELSE
	UPDATE BOOKS SET BOOKNum = BOOKNum + @Quantity -@q WHERE BOOKid  = @BookID
	END


UPDATE BOOKRecord 
SET Quantity =10
WHERE id =4
GO
SELECT * FROM BOOKS
GO
SELECT * FROM BOOKRecord
GO