最近被問到一個問題,如何防止資料表被意外刪除
有兩種方式:
第一種是DBA的慣用的老技巧Create View With SchemaBinding
第二種是SQL Server 2005才開始有的DDL Trigger
[方法1] SchemaBinding
-- 新增demo資料庫
USE master
GO
CREATE DATABASE MyDB
GO
-- 新增一個測試客戶資料表
USE MyDB
GO
CREATE TABLE dbo.customer
(
cust_id int PRIMARY KEY,
cust_name varchar(20),
cust_telephone varchar(20)
);
GO
-- 新增一個使用SCHEMA BINDING的VIEW
CREATE VIEW dbo.vCustomer
WITH SCHEMABINDING
AS
SELECT [cust_id]
,[cust_name]
,[cust_telephone]
FROM [dbo].[customer]
GO
-- 無法移除資料表(因資料表已被vCustomer所referenced)
DROP TABLE customer
GO
-- 無法移除現有的欄位(已被vCustomer所referenced)
ALTER TABLE [dbo].[customer]
DROP COLUMN cust_telephone ;
GO
-- 可新增欄位
ALTER TABLE [dbo].[customer]
ADD cust_Address VARCHAR(20) ;
GO
-- 可移除未被vCustomer所referenced的欄位
ALTER TABLE [dbo].[customer]
DROP COLUMN cust_Address ;
GO
-- 移除demo資料庫
USE master
GO
DROP DATABASE MyDB
GO
[方法2] DDL Trigger
-- 新增demo資料庫
USE master
GO
CREATE DATABASE DEMO_DDL_TRIGGER
GO
-- 新增2個測試資料表
USE DEMO_DDL_TRIGGER
GO
CREATE TABLE dbo.customer
(
cust_id int PRIMARY KEY,
cust_name varchar(20),
cust_telephone varchar(20)
);
GO
CREATE TABLE dbo.orders
(
order_id int PRIMARY KEY,
product_name varchar(20)
);
GO
-- 新增一個Production Table的資料表
CREATE TABLE dbo.ProductionTable
(
prodtable_id int PRIMARY KEY,
table_name varchar(50)
);
GO
-- INSERT一筆customer
INSERT INTO dbo.ProductionTable
VALUES(1,'customer');
GO
SELECT * FROM dbo.ProductionTable;
GO
-- 新增一個DDL Trigger on Database level
CREATE TRIGGER [Tgr_ChkProductionTable]
ON DATABASE
FOR DROP_TABLE
AS
--PRINT 'You must disable DDL Trigger "[Tgr_ChkProductionTable]" to drop or alter tables!'
declare @tablename varchar(50)
SELECT @tablename = EVENTDATA().value
('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
IF EXISTS (SELECT prodtable_id FROM [ProductionTable] WHERE table_name=@tablename)
BEGIN
RAISERROR ('You must delete the record from ProductionTable or disable DDL Trigger "[Tgr_ChkProductionTable]" before you drop the table!',10, 1)
ROLLBACK
END
;
GO
-- 測試無法刪除資料表
DROP TABLE dbo.customer;
GO
-- 不在production_table資料表內有一筆紀錄則可被刪除
DROP TABLE dbo.orders;
GO
-- 移除DDL Trigger
DROP TRIGGER [Tgr_ChkProductionTable]
ON DATABASE
GO
-- 移除demo資料庫
USE master
GO
DROP DATABASE DEMO_DDL_TRIGGER
GO
