November 23, 2009 at 9:17 am
Is it possible to have a second insert trigger (ie I all ready have one insert trigger that monitors for failures) in the same table? But this one always gives me nulls on the table it inserts into.
ALTER TRIGGER [dbo].[jobstatus]
on [dbo].[sysjobhistory]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if exists (select * from inserted where run_status = 1 and step_name <> '(job outcome)')
begin
insert into admin..jobssql (servername, jobname, starttime, endtime, status)
select server, left( name,50), substring(str(run_date),5,2) + '/'+
right (str(run_date),2) + '/' +
ltrim( left (str(run_date),6) ) + ' ' +
ltrim (left (str(run_time), 6)) + ':' +
substring (str(run_time),7,2) as startdt,
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime,
run =
case run_status
when 1 then 0
when 0 then 1
end
from inserted
joinsysjobs
oninserted.job_id = sysjobs.job_id
where step_id = 0 and inserted.run_status = 1
End
November 23, 2009 at 9:27 am
You can have as many AFTER INSERT triggers as you like. It's the INSTEAD OF triggers that are limited to one per operation.
What's that trigger supposed to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2009 at 9:31 am
It's supposed to insert the job history into a new table, but I only get nulls inserted. The query works ok as is:
select server, left( name,50), substring(str(run_date),5,2) + '/'+
right (str(run_date),2) + '/' +
ltrim( left (str(run_date),6) ) + ' ' +
ltrim (left (str(run_time), 6)) + ':' +
substring (str(run_time),7,2) as startdt,
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime,
run =
case run_status
when 1 then 0
when 0 then 1
end
from sysjobhistory
joinsysjobs
onsysjobhistory.job_id = sysjobs.job_id
where step_id = 0
order by end_datetime desc
Of course the trigger has "from inserted" instead of sysjobhistor as above.
November 24, 2009 at 6:57 am
So what are you getting NULL's in? The entire row or specific columns?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply