Click here to Skip to main content
15,887,917 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[leave] ON [dbo].[attLeaveApplication]
AFTER UPDATE

AS 
declare @leaveAppId as numeric
declare @leaveId as numeric
declare @empid as varchar
declare @duration as numeric
declare @leavetype as varchar
declare @OP as numeric

select @leaveAppId=[LeaveAppId],@empid=[EmpID],@duration=[ApprDuration],@leaveId=[LeaveId] from inserted where [ApprFlag]=1

select @leavetype=[LeaveType] from attLeaveTypeMaster where [LeaveId]=@leaveId


if @leavetype='CL'
Begin
select @OP=[CLCL] from attLeaveTransaction where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
set @OP = @OP - @duration
update attLeaveTransaction set [CLCL]=@OP where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
END
if @leavetype='PL'
Begin
select @OP=[PLCL] from attLeaveTransaction where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
set @OP = @OP - @duration
update attLeaveTransaction set [PLCL]=@OP where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
END
if @leavetype='SL'
Begin
select @OP=[SLCL] from attLeaveTransaction where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
set @OP = @OP - @duration
update attLeaveTransaction set [SLCL]=@OP where [EmpId]=@empid and DATEPART(Month,[Month])=DATEPART(Month,GetDate())
END

Help Me with this trigger. It is not triggered when update is performed.


Can u tell me how can i debug trigger in sql server?
Posted
Updated 24-Jul-10 1:13am
v3

1 solution

Well, looking at the Trigger queries and AFAICU, do you need an Update trigger or an INSERT trigger?

Might be you want that, the moment Table "attLeaveApplication" get an application, you want to use this trigger. If so, you need to use INSERT trigger.

In case, of update of Table "attLeaveApplication", trigger looks fine. Please check the execution step wise, also use PROFILER if need to see what all are getting executed on DB.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900