Trigger to check if a column is updated and another one has a specific value

by Abdullah Salim   Last Updated September 03, 2018 10:06 AM

I'm writing a trigger for a SQL Server database. What I want is, when a table is updated, check if a specific column is updated or not and if another column has a value = 'W' or not. If it is so, the date column should be updated in the old row and a new row should be inserted.

This is my table:

CREATE TABLE [dbo].[member_in_test] (
[id]          INT          IDENTITY (1, 1) NOT NULL,
[chart_no]    INT          NULL,
[team_id]     INT          NOT NULL,
[position_id] INT          NULL,
[member_id]   INT          NOT NULL,
[FromD]       DATE         NOT NULL,
[ToD]         DATE         NULL,
[credence]    NVARCHAR (1) DEFAULT ('W') NOT NULL,
CONSTRAINT [member_in_pkt] PRIMARY KEY CLUSTERED ([id] ASC);)

And this is my IF statement: if position_id is updated and credence not equal to 'W', the ToD will be updated and a new row will be inserted into the table.

if update(position_id) && credence !='W'
begin
update member_in set ToD= GETDATE() 

insert into member_in(team_id,position_id,member_id,fromD,chart_no) values(@team_id,@Position_id,@Member_ID ,getdate(),@Chart_no);
end

So could anyone help me to create the trigger?



Related Questions



creating a trigger after update exclude a column?

Updated September 06, 2018 18:06 PM

Checking update value before update

Updated December 04, 2018 11:06 AM

Audit Trail trigger can't detect if dates are the same

Updated September 12, 2017 04:06 AM

UPDATE Trigger with condition

Updated February 05, 2018 20:06 PM