--   【應(yīng)用案例】圖書館有時(shí)會(huì)對(duì)某圖書的復(fù)本量進(jìn)行更新，如增購某圖書
-- 復(fù)本，或因各種原因?qū)е履硤D書丟失部分復(fù)本。復(fù)本量的更改直接導(dǎo)致庫
-- 存量的改變，但為了不因誤操作破壞數(shù)據(jù)完整性，我們不能對(duì)庫存量直接
-- 修改，可以通過觸發(fā)器，監(jiān)測復(fù)本量的改變，自動(dòng)修改庫存量，這就可以
-- 用到對(duì)選定列的更新進(jìn)行監(jiān)測的方法。

-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'book_message' 
	   AND 	  type = 'TR')
    DROP TRIGGER book_message
GO

CREATE TRIGGER book_message
ON BOOK FOR UPDATE
AS
IF UPDATE(復(fù)本量)
BEGIN
     DECLARE @ISBN ISBN
     DECLARE @orignal_FBL int,@orignal_KCL int,
             @cur_JYL int, @aim_FBL int
     SELECT @orignal_FBL=復(fù)本量,
      	    @orignal_KCL=庫存量,
            @ISBN=ISBN
     FROM DELETED
     SELECT @aim_FBL=復(fù)本量
     FROM INSERTED
     IF @aim_FBL>=(@orignal_FBL-@orignal_KCL)
        BEGIN
            UPDATE BOOK 
            SET 庫存量=@orignal_KCL+(@aim_FBL-@orignal_FBL)
            WHERE ISBN=@ISBN
        END
     ELSE 
        BEGIN 
           RAISERROR('對(duì)復(fù)本量的更新違反了用戶定義數(shù)據(jù)完整性',16,10)
           ROLLBACK TRANSACTION 
        END
END
GO







--在XS表上設(shè)計(jì)觸發(fā)器，使得畢業(yè)時(shí)未還完書的學(xué)生不得注銷借書證
--（即：如果學(xué)生借閱量>0，不得刪除該學(xué)生信息）
-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'Student_message' 
	   AND 	  type = 'TR')
    DROP TRIGGER Student_message
GO

CREATE TRIGGER Student_message
ON XS
FOR DELETE 
AS 
BEGIN
    DECLARE @借書量 int
    DECLARE stu_cursor CURSOR
    FAST_FORWARD READ_ONLY
    FOR     
            SELECT 借書量
            FROM DELETED
    OPEN stu_cursor
    FETCH NEXT FROM stu_cursor 
    INTO @借書量
    WHILE @@FETCH_STATUS = 0
    BEGIN
         IF  @借書量>0
         BEGIN
                RAISERROR ('該同學(xué)還存在借閱記錄，不能注銷借書證！', 16, 10)
                ROLLBACK TRANSACTION
                RETURN
         END
                FETCH NEXT FROM stu_cursor 
                INTO @借書量
         END
    CLOSE stu_cursor
    DEALLOCATE stu_cursor
END
GO