Trigger that creates a new trigger
-
Hello1!!!
I'm wondering if a trigger is able to create a trigger dinamically
I'd like to create a new one over a table on each [Configuration] table insert.
The table that would recieve the new trigger would depend on the value of the new [Configuration] register
I''v looked at books online, and there seems to be no restrictions about this.... but SQL Analizer finds wrong sintax on this little piece of code:
CREATE TRIGGER [T1] ON [dbo].[Configuration]
FOR INSERT AS
CREATE TRIGGER [T2] ON [dbo].[AnyTable]
FOR INSERT, UPDATE, DELETE
as
Set @i = GETDATE()
Thanks!!!
-
Will be possible with dynamic SQL. I really don't understand why would you want to do that.
SET NOCOUNT ON
GO
CREATE TABLE Configuration
(
MyID INT
)
GO
CREATE TABLE AnyTable
(
myID INT
)
GO
CREATE TRIGGER [T1] ON [dbo].[Configuration]
FOR INSERT AS
DECLARE @sql VARCHAR(2000)
SELECT @sql = 'IF OBJECT_ID(''T2'') IS NOT NULL DROP TRIGGER T2 GO'
SELECT @sql = 'CREATE TRIGGER [T2] ON [dbo].[AnyTable]
FOR INSERT, UPDATE, DELETE
as
DECLARE @i DATETIME
Set @i = GETDATE()'
EXECUTE (@SQL)
GO
INSERT INTO Configuration
VALUES (1)
GO
sp_helptext 'T2'
GO
DROP TABLE Configuration, AnyTable
Regards,
gova -
Govinn is right - you'll need to use dynamic SQL... This means that the security context of the user executing the statement causing the trigger to fire will be used, rather than the context of the trigger's owner. Having said that, I've never tried executing dynamic SQL from a trigger - spose it works though
As for why you want to do it, I can only assume that for each row in your configuration table, you want another table to be named based on some of the data in that row and have a trigger on that table.. All very well and good I suppose, but the logic might be made more clear by using a stored proc to handle the inserts into your config table? Also, if the extra tables you make all have the same structure, why not just add an extra column and make it part of the pkey - the column would be a link back to the configuration table.
Good luck
-
Hi!
Thanks for your comments. I did'nt fall into the dynamic sql trick.
thanks for reminding me!
I want this way.... 'cause I want the user to be able to assign wich tables are being AUDITED (every change in any register has to be traced), and as the Java programmers are doing all the work with Java Classes, we are using an intermediate HYBERNATE layer, wich is unable to call stored procedures... that's cause I'm using triggers.
Hope this makes sense.
TKX again!
-
Fair enough - more than one way to skin a cat
Just make sure you document it somewhere.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply