DDl Triggers for tables...

Hi,

I have a scenario in which I need to restrict schema changes for around 5 tables only in a database. When changes are done to the other tables, it should be allowed. I am planning to use DDL Trigger. But DDL Trigger is having only 2 options in the ON Clause like ON DATABASE and ON ALL SERVERS. If i use On Database I will not be able to make modifications in the other tables.

Is there any way i could achieve my requirement?

Regards,

Swapna.B.

Move the thread to "SQL Server Database Engine" forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1|||

If you define your trigger on the DDL_TABLE_EVENTS event group then, within the trigger, you can parse the EventData function's value to return the following values when an ALTER TABLE statement is issued:

<EVENT_INSTANCE>
 <EventType>type</EventType>
 <PostTime>date-time</PostTime>
 <SPID>spid</SPID>
 <ServerName>name</ServerName>
 <LoginName>name</LoginName>
 <UserName>name</UserName>
 <DatabaseName>name</DatabaseName>
 <SchemaName>name</SchemaName>
 <ObjectName>name</ObjectName>
 <ObjectType>type</ObjectType>
 <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Inside the trigger's code you should check to see if the ObjectName and SchemaName values match those of any of the tables that you want to preserve and then issue a rollback command if necessary.

Check out the EVENTDATA Function topic in BOL for more info.

Chris

|||

Hi,

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

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.

|||

Could you post the definitions of both your trigger and your stored proc?

Chris

|||

Hi Chris,

Here is the definition of my trigger and Stored Procedure.

Trigger :

USE [Jeux]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create trigger [DDL_TRG_DB] on database for ALTER_TABLE as

set ANSI_NULLS ON

set ANSI_PADDING ON

set ANSI_WARNINGS ON

set ARITHABORT ON

set CONCAT_NULL_YIELDS_NULL ON

set NUMERIC_ROUNDABORT OFF

set QUOTED_IDENTIFIER ON

declare @.EventData xml

set @.EventData=EventData()

exec sp_Sample @.EventData, 1

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_TRG_DB] ON DATABASE

Stored Procedure :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_Sample]

(

@.EventData xml

,@.procmapid int

)

AS

begin

set nocount on

if is_member('db_owner') <> 1

begin

raiserror (21050, 16, -1)

return (1)

end

-- validate the procmapid

if @.procmapid not in (1,2,3,4)

begin

raiserror(15021, 16, -1, '@.procmapid')

Rollback Transaction

Return (1);

end

declare @.object_name sysname

,@.object_owner sysname

,@.qual_object_name nvarchar(512) --qualified 3-part-name

,@.objid int

,@.objecttype varchar(32)

,@.encrypted nvarchar(32)

,@.pass_through_scripts nvarchar(max)

,@.eventDoc int

,@.db_name sysname

,@.targetobject nvarchar(51)

set @.targetobject=N''

-- parse event data

select @.object_name = event_instance.value('ObjectName[1]', 'sysname')

,@.object_owner = event_instance.value('SchemaName[1]', 'sysname')

,@.objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')

,@.encrypted = event_instance.value('(TSQLCommand/SetOptions/@.ENCRYPTED)[1]', 'nvarchar(32)')

,@.pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')

,@.targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')

FROM @.EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

select @.qual_object_name = QUOTENAME(@.object_owner) + N'.' + QUOTENAME(@.object_name)

select @.objid = object_id(@.qual_object_name)

select @.db_name=db_name()

select @.pass_through_scripts = sys.fn_replgetparsedddlcmd(@.pass_through_scripts

,N'ALTER'

,@.objecttype

,@.db_name

,@.object_owner

,@.object_name

,@.targetobject)

if UPPER(@.objecttype) != N'TABLE' and UPPER(@.objecttype) != N'TRIGGER'

begin

select @.pass_through_scripts = N'ALTER ' + @.objecttype + N' '

+ @.qual_object_name + N' '

+ @.pass_through_scripts

end

If (@.procmapid = 1)

begin

IF(@.object_name in (Select Article from MSsubscription_articles))

begin

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

Else

begin

Commit Transaction

Print 'Transaction Commited!!!!!'

end

end

end

GO

Kindly check and let me know.

Thanks and Regards,

Swapna.B.

|||

Try removing 'COMMIT TRANSACTION' from the second BEGIN END block at the end of your stored proc, see below, leave ROLLBACK TRANSACTION in place.

Chris

If (@.procmapid = 1)

BEGIN

IF(@.object_name in (SELECT Article from MSsubscription_articles))

BEGIN

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

--Else

--BEGIN

--Commit Transaction

--Print 'Transaction Commited!!!!!'

--end

end