CODEWITHSUNDEEP
sql-serversql-server-2008
themhz
themhz

Reputation: 8424

SQL SERVER 2008 TRIGGER ON CREATE TABLE

Is there a way to run some function like a trigger when a table is created in the database in SQL SERVER 2008?

Upvotes: 2

Views: 7951

Answers (2)

TyT
TyT

Reputation: 299

Yes a DDL Trigger. For example, here is some code I written to prevent some tables from being modified:

PRINT N'Create DDL trigger to prevent changes to various tables'
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER NoEditCertainTables ON DATABASE 
    FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS 

SET CONCAT_NULL_YIELDS_NULL ON

DECLARE @AffectedTable varchar(255) 
SELECT  @AffectedTable = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') 

--This is the name of the table(s) you dont want messed with
IF (@AffectedTable IN ('Table1','Table2','Table3'))
BEGIN
    ROLLBACK;
END 
SET CONCAT_NULL_YIELDS_NULL OFF      

GO

Upvotes: 5

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

Yes, it's called a DDL trigger. The documentation for CREATE TRIGGER has a sample for DROP_SYNONYM (a very questionable choice for an example) but you'll want the CREATE_TABLE event instead. A better starting point in understanding how they work is probably here:

http://msdn.microsoft.com/en-us/library/ms190989.aspx

If you have more specific details, e.g. what exactly do you want to pass to this function (I assume you mean procedure), or what does the procedure do, we can provide more useful and specific help.

Upvotes: 6

Related Questions