---14-1
USE BookShopDb 
GO
--插入触发器
CREATE TRIGGER Insert_Category
ON Category FOR INSERT
AS
   DECLARE @name VARCHAR(20)
   DECLARE @count INT
   SELECT @name=categoryName FROM inserted
   SELECT @count=count(*) FROM Category
   WHERE substring(CategoryName,1,4) LIKE @name+ '%'
   IF(@count>0) 
   BEGIN
   PRINT '不能插入相同的类别名称'
   ROLLBACK TRANSACTION
   END
---14-2
USE BookShopDb 
GO
--更新触发器
CREATE TRIGGER UPDATE_bookinfo
  ON BookInfo FOR UPDATE
  AS
    DECLARE @old DATETIME
    DECLARE @new DATETIME
    --获取旧的出版日期
    SELECT @old=PublishDate FROM deleted
    --获取新的出版日期
    SELECT @new=PublishDate FROM inserted
    --日期差值最大为天
    IF(datediff(dy,@old,@new)>=31)
    BEGIN
        PRINT '新的出版日期不能比旧出版日期大一个月'
        ROLLBACK TRANSACTION
    END
    
--测试代码：
-----将书号为'B0001'的书籍出版日期推后天
   UPDATE BookInfo SET PublishDate=PublishDate+40
   WHERE BookId='B0001'

---14-3
USE BookShopDb 
GO
--列级Update触发器
CREATE TRIGGER UPDATE_BookColumn
  ON BookInfo AFTER UPDATE
  AS
   IF (UPDATE(ISBN)) 
   BEGIN   
        PRINT 'ISBN一旦确定不能修改'
        RAISERROR('系统提示：ISBN不能修改！', 16, 1);
   END

---14-4
USE BookShopDb 
GO

CREATE TRIGGER DELETE_book
ON BookInfo AFTER DELETE
AS
  DECLARE @publishDate DATETIME
  SELECT @publishDate=publishDate FROM Deleted
  IF(@publishDate<'2000-1-1')
  BEGIN
       RAISERROR('2000年之前出版的书不能删除',16,1)
       ROLLBACK TRANSACTION
  END
 
 --14-5
USE BookShopDb 
GO

CREATE VIEW V_BookInfo
AS 
SELECT a.*,b.CategoryName,c.PublisherName
FROM BookInfo a,Category b,Publisher c
WHERE a.CategoryID=b.CategoryID
AND a.PublisherID=c.PublisherID
GO
--创建删除触发器：
CREATE TRIGGER vbook_DELETE
ON V_BookInfo INSTEAD OF DELETE
AS
  DELETE FROM BookInfo 
  WHERE bookId in
  (SELECT BookId FROM Deleted)

--测试语句：
DELETE FROM V_BookInfo WHERE BookId='B0014'
