DDL Trigger to update Instead Of Insert trigger

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 [ioiApplicationTrigger]
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 [UpdateStandardTriggers]
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 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' 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?
PeterPeter,
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.childrensoncologygroup.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 [ioiApplicationTrigger]
> 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 [UpdateStandardTriggers]
> 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 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' 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
>
>