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
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?