MS SQL Server - DDL Trigger to update Instead Of Insert trigger

Author

DDL Trigger to update Instead Of Insert trigger

Peter Gloor

2006-03-08, 1:23 pm

Hello NG,

In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.

As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER & #91;ioiApplicationTr
igger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted

This works as expected.

In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER & #91;UpdateStandardTr
iggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @trigger_name nvarchar(max);
DECLARE @table_name nvarchar(max);
DECLARE @data XML

-- Get table name from eventdata
SET @data = EVENTDATA()
SET @table_name =
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')

EXEC UPDATE_IOI_TRIGGER @table_name
END

The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.

However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigg
er'.
Cannot create trigger 'ioiApplicationTrigg
er' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.

In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.

Any idea how I can achieve what I tried to explain before?

Peter

Daniel Jameson

2006-03-08, 8:23 pm

Peter,

Why not just use column level DENY, e.g.,

DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
....

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www. childrensoncologygro
up.org
"Peter Gloor" <p_gloor@hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@TK2MSFTNGP15.phx.gbl...
> Hello NG,
>
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
>
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER & #91;ioiApplicationTr
igger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
>
> This works as expected.
>
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER & #91;UpdateStandardTr
iggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @trigger_name nvarchar(max);
> DECLARE @table_name nvarchar(max);
> DECLARE @data XML
>
> -- Get table name from eventdata
> SET @data = EVENTDATA()
> SET @table_name =
> @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
>
> EXEC UPDATE_IOI_TRIGGER @table_name
> END
>
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
>
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigg
er'.
> Cannot create trigger 'ioiApplicationTrigg
er' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
>
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
>
> Any idea how I can achieve what I tried to explain before?
>
> Peter
>
>
>
>

Sponsored Links