May 30, 2014 at 8:32 am
ALTER TRIGGER [dbo].[Trigger1]
ON [dbo].[Table1] with execute as SELF
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT Colum1 FROM Inserted) = 'Entry'
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'Job1';
REVERT
END
END
I am trying to create a trigger so every time a entry is made on a table, and the Colum1 is 'entry', it starts a job. But the users running the inserts do not have permission to Start jobs so I need to make it run as a super user. Where do i put the syntax in here? I Have tried Execute as login 'superuser' before the exec statement but it errors on the principal not being valid
May 30, 2014 at 8:52 am
SQLSteve (5/30/2014)
ALTER TRIGGER [dbo].[Trigger1]ON [dbo].[Table1] with execute as SELF
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT Colum1 FROM Inserted) = 'Entry'
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = 'Job1';
REVERT
END
END
I am trying to create a trigger so every time a entry is made on a table, and the Colum1 is 'entry', it starts a job. But the users running the inserts do not have permission to Start jobs so I need to make it run as a super user. Where do i put the syntax in here? I Have tried Execute as login 'superuser' before the exec statement but it errors on the principal not being valid
Your trigger suffer from a major issue. It cannot handle multiple row operations.
I would caution you that running a sql job from a trigger screams of poor design. This is highly likely to suffer greatly from performance issues. Triggers in general can be an issue but you must use a trigger it is generally a better practice to populate a staging table and then have a job that runs on a set interval.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply