轉：SQLServer串聯刪除樣本

SQLServer 2000與Oracle 8i相比較，在串聯刪除上有太多的限制

1. 表內自關聯不支援串聯刪除和串聯更新
2. 一個表內在有多個列同時關聯於另一個表時，不支援多個串聯刪除
3. 還有什麼迴圈串聯刪除限制，等等
4. .......

一大堆限制，如果要遷移Oracle到SqlServer麻煩多多，特別對於用於構造樹型結構的表內自關聯（比如部門表）的串聯刪除特別討厭

考慮來考慮去，對於SqlServer不能支援的諸多串聯刪除只有採用最原始的方法，徹底不用外部索引鍵關聯，而是用觸發器來解決

以下是經過驗證的觸發器實現範例（自關聯例子）

放棄外部索引鍵關聯後所有的完整性檢查都要通過觸發器實現

--DELETE 串聯刪除，先刪除，再刪除所有級聯的記錄，採用遞迴觸發器，當然要求資料庫支援遞迴觸發器功能開啟（資料庫屬性設定中開啟他），不過SqlServer只支援32級的遞迴啊
 IF EXISTS (SELECT NAME FROM SYSOBJECTS
      WHERE NAME = 'DEL_DOCUMENTTYPE_001' AND TYPE = 'TR')
   DROP TRIGGER DEL_DOCUMENTTYPE_001
 GO
 
 CREATE TRIGGER DEL_DOCUMENTTYPE_001
 ON dbo.DOCUMENTTYPE
 FOR DELETE
 AS 
 IF (SELECT COUNT(*) FROM DELETED) > 0
   DELETE FROM dbo.DOCUMENTTYPE WHERE UPNO IN (SELECT NO FROM DELETED)
 GO   
  

 

--INSERT  資料插入，驗證外部索引鍵關聯邏輯，錯誤拋出出錯資訊
 IF EXISTS (SELECT NAME FROM SYSOBJECTS
      WHERE NAME = 'INS_DOCUMENTTYPE_001' AND TYPE = 'TR')
   DROP TRIGGER INS_DOCUMENTTYPE_001
 GO
 
 CREATE TRIGGER INS_DOCUMENTTYPE_001
 ON dbo.DOCUMENTTYPE
 FOR INSERT
 AS 
 IF (SELECT COUNT(*) FROM INSERTED WHERE UPNO IS NOT NULL
      AND (UPNO = NO OR UPNO NOT IN (SELECT NO FROM dbo.DOCUMENTTYPE))) > 0
   BEGIN
    DELETE FROM dbo.DOCUMENTTYPE WHERE NO IN (SELECT NO FROM INSERTED)
    RAISERROR('在被參考資料表 dbo.DOCUMENTTYPE 中沒有與外鍵 UPNO 的引用列的列表匹配的主鍵或候選索引鍵。',16,1)
   END
 GO 
  
--UPDATE  資料更新，如果更新了外鍵，驗證外鍵存在
 IF EXISTS (SELECT NAME FROM SYSOBJECTS
      WHERE NAME = 'UPD_DOCUMENTTYPE_001' AND TYPE = 'TR')
   DROP TRIGGER UPD_DOCUMENTTYPE_001
 GO
 
 CREATE TRIGGER UPD_DOCUMENTTYPE_001
 ON dbo.DOCUMENTTYPE
 FOR UPDATE
 AS 
 IF (SELECT COUNT(*) FROM INSERTED WHERE UPNO IS NOT NULL
      AND (UPNO = NO OR UPNO NOT IN (SELECT NO FROM dbo.DOCUMENTTYPE))) > 0
   BEGIN
           SET IDENTITY_INSERT dbo.DOCUMENTTYPE ON
    DELETE FROM dbo.DOCUMENTTYPE WHERE NO IN (SELECT NO FROM INSERTED)
--    INSERT INTO dbo.DOCUMENTTYPE SELECT * FROM INSERTED
    SELECT * INTO dbo.DOCUMENTTYPE FROM INSERTED
    SET IDENTITY_INSERT dbo.DOCUMENTTYPE OFF
    RAISERROR('在被參考資料表 dbo.DOCUMENTTYPE 中沒有與外鍵 UPNO 的引用列的列表匹配的主鍵或候選索引鍵。',16,1)
   END
 GO