Create trigger in a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create trigger in a stored procedure

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-11 : 09:41:54

Hi
I try to create an trigger JIT (just in time) in a stored procedure
But it seems not to cooperate/work very well.

what am i doing wrong here ? Isn't it possible at all ?

CREATE PROCEDURE dbo.[CreateNewTrigger]
(
@TriggerName nvarchar(255),
@DstTableName nvarchar(255)
)
AS
BEGIN

EXEC(
'CREATE TRIGGER dbo.['+@TriggerName+'] '+
' ON '+@DstTableName+' '+
'AFTER UPDATE, INSERT, DELETE '+
'AS '+
'BEGIN '+
'SELECT * FROM dbo.[MY_TABLE] '+
'END')

END

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-11 : 14:50:44

Why are you putting this into a stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-19 : 14:50:51

@tkizer:

Sorry for my late respond:
The reason is that i want to create an new trigger and associate it to the new table that has been adopted to the database.

When an schedule task triggers an check-events to look after new tables or changes, then it will associate the new table to have an trigger to it.
The trigger it self can be whatever i want it to be, or what its needs is (for instance to calculate and produce reports).
With this, the system can become more automatic updated, the opposite way is to call this manually everytime, very ineffective.

This combined with task schedule (job) solves the issue.
I find it very useful for many purposes.

If you find more way to do this, i would be pleased to here.

Thanks
V

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources