CREATE TABLE dbo.Foo (
keycol INT PRIMARY KEY,
datacol CHAR(1));
GO
-- Using view with SCHEMABINDING
CREATE VIEW DoNotDropFoo WITH SCHEMABINDING
AS
SELECT keycol, datacol
FROM dbo.Foo;
GO
-- Attempt to drop table Foo
DROP TABLE dbo.Foo;
GO
Msg 3729, Level 16, State 1, Line 3
Cannot DROP TABLE 'dbo.Foo' because it is being referenced by object 'DoNotDropFoo'.
-- Using DDL trigger
CREATE TRIGGER DoNotDropTables
ON DATABASE
FOR DROP_TABLE
AS
RAISERROR ('Cannot drop tables!', 10, 1);
ROLLBACK;
GO
-- Attempt to drop table Foo
DROP TABLE dbo.Foo;
GO
Cannot drop tables!
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted.
Labels: t-sql programming