Fun with Triggers - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Fun with Triggers

Author

Topic

MuadDBA

628 Posts

Posted - 2002-04-16 : 13:44:27

Hello,

In order to simplify my life, I have decided to set up a pseudo-master server (since I am not allowed to use our e-mail server for SQLserver messages) by writing a trigger based on the sysjobhistory table in the MSDB of each server. My trigger populates another table with the overall job status, and I copy the contents of that table out to another server which houses job information for all servers.

My problem is that, for some reason, it appears my trigger will fail every now and then, and when it does so, I lose some of my job history information from the sysjobistory table. For instance, the overall job status won't be there for one particular run, but if I show step details, I can see detail steps for that particular run. Can someone look at this trigger and tell me where I might be going wrong (other than in creating triggers on system tables?)

CREATE TRIGGER job_update ON [dbo].[sysjobhistory]
FOR INSERT
AS

declare @step_id tinyint
declare @run_status tinyint
select @step_id = step_id, @run_status = run_status from inserted

if @step_id = 0 and @run_status = 0
begin
insert into job_hist..daily_job_status select @@servername, name, step_name,
CASE when run_status = 1 then 'SUCCESS' when run_status = 0 then 'FAIL' else 'OTHER' end,
SUBSTRING(convert(varchar,convert(datetime,convert(varchar,run_date)),121),1,10)
+ ' ' +SUBSTRING(CONVERT(VARCHAR,RUN_TIME),1,2) + ':' + SUBSTRING(CONVERT(VARCHAR,RUN_TIME),3,2) + ':'
+ SUBSTRING(CONVERT(VARCHAR,RUN_TIME),5,2) + '.000' as run_dt from inserted a, sysjobs b
where a.job_id = b.job_id
end

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-16 : 14:44:03

You are generating invalid date values for runtimes with less than 6 characters. Try changing your substrings to look this.

CREATE TRIGGER job_update ON [dbo].[sysjobhistory]
FOR INSERT
AS
declare @step_id tinyint
declare @run_status tinyint
select @step_id = step_id, @run_status = run_status from inserted

if @step_id = 0 and @run_status = 0
begin
insert into job_hist..daily_job_status
select @@servername,
name,
step_name,
CASE
when run_status = 1 then 'SUCCESS'
when run_status = 0 then 'FAIL'
else 'OTHER'
end,
SUBSTRING(convert(varchar,convert(datetime,convert(varchar,run_date)),121),1,10) + ' ' +
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,RUN_TIME), 6),1,2) + ':' +
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,RUN_TIME), 6),3,2) + ':' +
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR,RUN_TIME), 6),5,2) + '.000' as run_dt
from inserted a,
sysjobs b
where a.job_id = b.job_id
end

Jeff Banschbach
Consultant, MCDBA

MuadDBA

628 Posts

Posted - 2002-04-16 : 15:08:18

Thanks Jeff,

I thought it might be related to tthe datetime values, but wasn't sure. Can you explain, though, why when the trigger fails, the row isn't inserted into the table it was originally intended for? Is this the way all triggers work? It isn't clear from the books online.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-16 : 15:28:41

The trigger is part of the insert transaction. If the trigger fails, the entire operation, including the original insert, is rolled back. This is the behavior for all triggers.

Be careful with this as this is included in books online. If you have a support issue on one of your servers you may not get much help from MS.

quote:
Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/16/2002 15:29:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources