0

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?

0

Your question is not entirely clear to me, but here is a start. It looks like you are trying to emulate a slowly changing dimension. I used DATETIME for FromD and ToD so you could better see the data changing.

If I have misunderstood your requirements, please update your question with more information.

--demo setup
drop table if exists dbo.member_in_test;
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]       DATETIME         NOT NULL,
[ToD]         DATETIME         NULL,
[credence]    NVARCHAR (1) DEFAULT ('W') NOT NULL,
CONSTRAINT [member_in_pkt] PRIMARY KEY CLUSTERED ([id] ASC));

insert into dbo.member_in_test(chart_no,team_id,position_id,member_id,FromD,ToD,credence) 
values(1,1,1,1,'2018-09-03',null,'A');
go

--Trigger definition
CREATE TRIGGER [dbo].[member_in_test_Modified] ON [dbo].[member_in_test]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    --Update the rows that were changed to have current date in ToD
    --We're only interested in rows where the position_id changed and
    --the original row had credence <> 'W'
    UPDATE m
    SET ToD = getdate()
    FROM dbo.member_in_test m
    JOIN deleted d ON d.id = m.id
    JOIN inserted i ON i.id = d.id
        AND i.position_id <> d.position_id
        AND d.credence <> 'W'

    --Insert into table all rows from inserted, but make ToD = null
    INSERT INTO dbo.member_in_test (
        chart_no
        ,team_id
        ,position_id
        ,member_id
        ,FromD
        ,ToD
        ,credence
        )
    SELECT chart_no
        ,team_id
        ,position_id
        ,member_id
        ,getdate()
        ,NULL
        ,credence
    FROM inserted
END;
GO

-----------------------------------------------------------------
--Verification
--select data before and after update to verify trigger execution
select * from dbo.member_in_test;
go
update dbo.member_in_test set position_id = 2;
go
select * from dbo.member_in_test;

Date before update

| id | chart_no | team_id | position_id | member_id | FromD                   | ToD  | credence |
|----|----------|---------|-------------|-----------|-------------------------|------|----------|
| 1  | 1        | 1       | 1           | 1         | 2018-09-03 00:00:00.000 | NULL | A        |

Data after update

| id | chart_no | team_id | position_id | member_id | FromD                   | ToD                     | credence |
|----|----------|---------|-------------|-----------|-------------------------|-------------------------|----------|
| 1  | 1        | 1       | 2           | 1         | 2018-09-03 00:00:00.000 | 2018-09-03 06:15:33.740 | A        |
| 2  | 1        | 1       | 2           | 1         | 2018-09-03 06:15:33.740 | NULL                    | A        |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.