DDL Trigger...

Hi,

I have a scenario in which i need to restrict the schema changes of around 5 tables in a database. If I use ON Database option of DDL Trigger, the restriction is imposed on the entire database. But the requirement is retricting only 5 tables in the database, since other tables will undergo some schame changes in the future.

Is there anyway I could achieve this requirement?

Thanks,

Swapna.B.

Take a look into the eventdata function. It can be used within your code to scope the restriction to a specific set of tables.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/675b8320-9c73-4526-bd2f-91ba42c1b604.htm

|||

Hi,

I have achieved the requirement using eventdata function and validating the values in a separate sp. Thanks for the help provided.

I have the DDL trigger which calls a stored procedure. This SP does the segregating of values from eventdata function , validating those values and commits or rollsback according to the objectname retrieved. Now its working fine. But i face one error like the one below.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

One more point here is when we try to execute a batch of alter statements without go command in the database in which the DDL trigger is present, Only the first one gets executed other statements are not getting considered. Is this anyway related to the error above?

Thanks and Regards,

Swapna.B.