CODEWITHSUNDEEP
sql-servert-sqltriggers
Mohammad Feroz Khan
Mohammad Feroz Khan

Reputation: 11

Fire SQL Trigger When Column Value Changes

I am adding the date to a column in SQL when the 'workstatus' is 'completed', but my problem is, when I open and save the same job again in the software, it runs the trigger and changes the date again to a new value which I don't want.

I want the trigger to run only if the 'workstatus' value is something else than 'completed'.

    GO
/****** Object:  Trigger [dbo].[TRJCD_JOBREQUEST]    Script Date: 06/25/2021 15:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER            TRIGGER [dbo].[TRJCD_JOBREQUEST] ON [dbo].[TBL_JOBREQUEST] 
AFTER UPDATE,INSERT

AS

if (Update (workstatus))

begin

DECLARE @Jobcompletiondate datetime 
DECLARE @workstatus VARCHAR(15)
DECLARE @jobid int


select @workstatus = workstatus from inserted
select @jobid = jobid from inserted
select @Jobcompletiondate = GETDATE() 

begin

if @workstatus='Completed'


    update TBL_JOBREQUEST set JobCompDate=@Jobcompletiondate where jobid = @jobid 
end
end

Upvotes: 0

Views: 2040

Answers (2)

Stu
Stu

Reputation: 32579

The following is how you should construct your trigger.

There is no need to assign any values to variables, triggers fire once per batch and always operate on the set of updated rows.

If you update a status to Completed you need to check it's not currently Completed, also if you want to retain the first JobCompDate even if the status is amended afterwards simply use a case expression to only update the column where it's currently NULL.

create or alter trigger [dbo].[TRJCD_JOBREQUEST] on [dbo].[TBL_JOBREQUEST] 
after update,insert
as
if @@RowCount=0 return

set nocount on
if Update (workstatus)
begin

    update t set
        t.JobCompDate=case when t.JobCompDate is null then GetDate() else t.JobCompDate end
    from inserted i join TBL_JOBREQUEST t on t.jobid=i.jobid
    where i.workstatus='Completed' 
    and not exists (
      select * from deleted d 
      where d.jobid=i.jobid and d.workstatus=i.workstatus
    )

end

Upvotes: 1

Attie Wagner
Attie Wagner

Reputation: 1362

Please note that I do not have your data set, so I'm unable to test the trigger, however, based on what you provided in your question, I believe this is the answer you are seeking:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER            TRIGGER [dbo].[TRJCD_JOBREQUEST] ON [dbo].[TBL_JOBREQUEST] 
AFTER UPDATE,INSERT

AS

if (Update (workstatus))

begin

DECLARE @Jobcompletiondate datetime 
DECLARE @currentworkstatus VARCHAR(15)
DECLARE @oldworkstatus VARCHAR(15)
DECLARE @jobid int


select @oldworkstatus = workstatus from deleted
select @currentworkstatus = workstatus from inserted
select @jobid = jobid from inserted
select @Jobcompletiondate = GETDATE() 

begin

if @currentworkstatus='Completed' and @oldworkstatus <> 'Completed'


    update TBL_JOBREQUEST set JobCompDate=@Jobcompletiondate where jobid = @jobid 
end
end

You needed to check if the deleted workstatus does not equal Completed and only then should the trigger fire.

Upvotes: 0

Related Questions