Trigger is not working after 12/21/2007

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)

Trigger is not working after 12/21/2007

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2008-01-16 : 12:35:42

Hi Experts,
The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?

Trigger

USE [personnelreq]
GO
/****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*****************************************************************************************************************************************************************
* Trigger Name: tgrTracking
* Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition
* requisitions when the requisitions are emailed, transferred, or completed.
* History
* TKT/CO # Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 Zsofia Horompoli Created
* CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events
*****************************************************************************************************************************************************************/
CREATE TRIGGER [tgrTracking] ON [dbo].[Log2]
FOR INSERT
AS

DECLARE @reqId int -- store requisition ID
DECLARE @eventId int -- event value inserted
DECLARE @parentId int -- store parent requisition ID
DECLARE @eventTime datetime -- store date/time of event
DECLARE @email varchar(50) -- store email address
DECLARE @logId int -- store ID assigned to the record in the Log2 table
DECLARE @location varchar(30) -- Location
DECLARE @title varchar(30) -- Job title
DECLARE @multiReq int -- > 0 = Multi NSA
DECLARE @origMail varchar(50) -- Originator's email address
DECLARE @subject varchar(200) -- Email subject line
DECLARE @from varchar(50) -- From email
DECLARE @body varchar(8000) -- Email body
DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus)
DECLARE @sReqType varchar(30) -- Requisition type description
DECLARE @appEmail varchar(500) -- Approvers' email addresses

SELECT @from = 'compensationandbenefits@west.com'

DECLARE @inserted_rows AS CURSOR -- hold the inserted rows
SET @inserted_rows = CURSOR FOR
SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows

OPEN @inserted_rows
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Is this an email/transfer/complete/deny event?
IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10)
BEGIN -- Yes, is this a deny event?
IF (@eventId = 0)
BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?

SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype in (0,1) and id = @reqId and
(SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2
END
ELSE IF (@eventId = 6)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1,6) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE IF (@eventId = 7 OR @eventId = 10)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE
BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END

--Is this a requisition to be tracked?
IF @parentId is not NULL
BEGIN --Yes, is this a transfer event?
IF @eventId = 6
BEGIN --Yes, retrieve information for email
SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype
FROM reqs2 R
LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION
LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE
WHERE R.id = @reqId

-- Get email addresses to use
EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT

--Set requisition type description
IF @reqType = 0
BEGIN --Replacement requisition
SELECT @sReqType = 'Replacement'
END
ELSE IF @reqType = 1 AND @multiReq > 0
BEGIN --Multi-NSA
SELECT @sReqType = 'Multiple Net Staff Addition'
END
ELSE IF @reqType = 1
BEGIN --NSA
SELECT @sReqType = 'Net Staff Addition'
END
ELSE IF @reqType = 6
BEGIN --Bonus
SELECT @sReqType = 'Bonus'
END

--Build subject line
--Do we have a location?
IF @location IS NULL
BEGIN --No, default to Nothing
SELECT @location = ''
END

--Do we have a job title?
IF @title IS NULL
BEGIN --No, default to Nothing
SELECT @title = ''
END

SELECT @subject = @location + ': ' + @sReqType + ': ' + CAST(@reqId AS varchar(15)) + ': ' + @title

--Set email body
IF @reqType = 6 -- If Bonus Req
BEGIN
SELECT @body = 'Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.'
SELECT @body = @body + 'If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .'
END
ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req
BEGIN
SELECT @body = 'Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.'
SELECT @body = @body + 'If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .'
END

--Send email
EXEC usp_SMTPMail @SenderAddress = @from, @RecipientAddress = @origMail, @Subject = @subject, @Body = @body, @Cc = @appEmail
END

-- Call the CalcTrackingHours stored procedure
IF @reqType IN (0,1)
BEGIN
EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId
END
END
END
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
END

--clean up
CLOSE @inserted_rows
DEALLOCATE @inserted_rows

Stored Procedure is

USE [personnelreq]
GO
/****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/

/*****************************************************************************************************************************************************************
* Stored Procedure Name: CalcTrackingHours
* Description: Determines the appropriate tracking level, the number of business hours the requisition
* spent at the current tracking level and inserts this information into the timeLog table
* History
* Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 syszxh Created
*****************************************************************************************************************************************************************/
CREATE procedure [dbo].[CalcTrackingHours]
@event int, -- 4=Emailed,6=Transferred,7=Completed
@reqId int, -- Requisition Id
@emailTo varchar(50), -- Email address requisition was forwarded to
@endDateTime datetime, -- Date/time of event
@logId int -- ID assigned to new record in the Log2 table
as
set nocount on

declare @currentUserType as int -- Current user type
declare @startDateTime as datetime -- Date/time requisition arrived at current step
declare @newUserType as int -- New user type
declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table
declare @newTrackLevel as varchar(5) -- New tracking level
declare @trackEmail as varchar(50) -- Email address at current tracking level
declare @count as int -- counter variable
declare @errorValue as int -- Error flag (1 = start date/time missing)
declare @trackHours as decimal(10,2)

select @errorValue = 0
select @startDateTime = NULL

-- Retrieve last email's user type, date/time stamp, and email address
select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid
from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid
where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc

-- Last email found?
if @startDateTime is NULL
begin
-- No, this is the very first email, retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo

-- Is new user @C&B?
if @newUserType = 2
begin -- Yes, set new tracking level
select @newTrackLevel = 'cb1'
end
else
begin -- No, set new tracking level
select @newTrackLevel = 'dept'
end
end
else
begin
-- Is this event = email?
if @event = 4
begin
-- Retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo

-- Is the current user the same as the new user?
if @currentUserType = @newUserType and @emailTo = @trackEmail
begin
-- Yes, get latest level
select top 1 @newTrackLevel = trackLevel
from timeLog
where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime
order by dateIn desc

select @currentTrackLevel = @newTrackLevel
end
-- Is the current user type @C&B?
else if @currentUserType <> 2 or @currentUserType is NULL
begin
-- No, retrieve current tracking level
exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output
-- Is the new user type @C&B?
if @newUserType = 2
begin -- Yes, retrieve new tracking level
exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output
end
else -- No, retrieve new tracking level
begin
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
end
end
else
begin
-- Yes, retrieve new tracking level
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
-- Retrieve current tracking level
exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output
end
end
-- Is this event = transfer/completed?
else if @event = 6 or @event = 7 or @event = 10
begin
-- No, any existing entries for exec3?
if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb5
select @currentTrackLevel = 'cb5'
end
-- Any existing entries for exec2?
else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb4
select @currentTrackLevel = 'cb4'
end
-- Any existing entries for exec1?
else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb3
select @currentTrackLevel = 'cb3'
end
-- Any existing entries for acct?
else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb2
select @currentTrackLevel = 'cb2'
end
else
begin
-- Set current tracking level to cb1
select @currentTrackLevel = 'cb1'
end
end

-- Calculate hours worked
if @event <> 10
begin
exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output

-- Update information in the timeLog table
update timeLog
set dateOut = @endDateTime, trackHours = @trackHours
where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31'
end
end

-- Is this event = email?
if @event = 4 and @newTrackLevel is not NULL
begin
-- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31')
end
-- Is this event = reopen?
else if @event = 10 and @trackEmail is not NULL
begin -- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31')
end

endProcedure:
Return @errorValue

-Thanks N Regards,
Kanthi.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-17 : 06:29:31

Is the trigger being fired?
Does it complete successfully?

Try using the profiler to see what's happening and put trace statements in the trigger.
Is email working - that's often the problem with anythnig that tries to send in-line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources