Call trigger when new trigger is created

Is it at all possible to call a trigger when a new trigger is created?If you are using SQL Server 2005, take a look at DDL triggers.|||

Triggers are invoked by the engine based on certain actions (DML and DDL & others in SQL Server 2005). So if you are on SQL Server 2005, you can use a DDL trigger to create one for CREATE TRIGGER action. This will fire the DDL trigger. There is no way to explicitly fire a trigger (by name) without invoking the action that causes the trigger to be fired. See links below for more details on DDL triggers in SQL Server 2005:

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

http://msdn2.microsoft.com/en-us/library/ms189599.aspx

http://msdn2.microsoft.com/en-us/library/ms189599.aspx

http://msdn2.microsoft.com/en-us/library/ms186406.aspx

You can also create event notifications in SQL Server 2005 for DDL actions, receive message in a service broker queue which can invoke a SP that performs your logic. But this is more elaborate mechanism and it depends on your needs.

|||

Thank you for the information. I am trying to write the trigger, but I get an error...

Line 3: Incorrect syntax near 'CREATE_TRIGGER'.

Here is what I have for my trigger:

CREATE TRIGGER WriteReg

ON dbo.Toc

FOR CREATE_TRIGGER

AS

EXEC master ..xp_cmdshell "C:\Path\Name.exe"

GO

Does anyone have any ideas on what I need to do differently? Is it because I'm using SQL Server 2005 Express, not the full edition?

Thanks

|||Please check the Books Online for complete syntax of DDL triggers. DDL triggers operate at server or database scope. You cannot specify an object explicitly like DML triggers. So the ON clause should be either DATABASE or ALL SERVER. To check for specific objects, you would use the EVENTDATA() value available inside the trigger.|||I have tried using DATABASE instead of the table name, but it gets an error saying "Incorrect syntax near keyword 'DATABASE'". Any ideas why? Could it be because I'm using the Express edition of SQL Server 2005?|||

Below works for me:

Code Snippet

create trigger TrapCreateTrg
on database
after CREATE_TRIGGER
AS
return;

Please post the code that doesn't work for you.

|||I have desided to go with a different route. I created a new table to track information about my application and I put a trigger on that table.