· 7 years ago · Nov 28, 2018, 02:06 PM
1CREATE TABLE [dbo].[member_in_test] (
2[id] INT IDENTITY (1, 1) NOT NULL,
3[chart_no] INT NULL,
4[team_id] INT NOT NULL,
5[position_id] INT NULL,
6[member_id] INT NOT NULL,
7[FromD] DATE NOT NULL,
8[ToD] DATE NULL,
9[credence] NVARCHAR (1) DEFAULT ('W') NOT NULL,
10CONSTRAINT [member_in_pkt] PRIMARY KEY CLUSTERED ([id] ASC);)
11
12if update(position_id) && credence !='W'
13begin
14update member_in set ToD= GETDATE()
15
16insert into member_in(team_id,position_id,member_id,fromD,chart_no) values(@team_id,@Position_id,@Member_ID ,getdate(),@Chart_no);
17end
18
19--demo setup
20drop table if exists dbo.member_in_test;
21CREATE TABLE [dbo].[member_in_test] (
22[id] INT IDENTITY (1, 1) NOT NULL,
23[chart_no] INT NULL,
24[team_id] INT NOT NULL,
25[position_id] INT NULL,
26[member_id] INT NOT NULL,
27[FromD] DATETIME NOT NULL,
28[ToD] DATETIME NULL,
29[credence] NVARCHAR (1) DEFAULT ('W') NOT NULL,
30CONSTRAINT [member_in_pkt] PRIMARY KEY CLUSTERED ([id] ASC));
31
32insert into dbo.member_in_test(chart_no,team_id,position_id,member_id,FromD,ToD,credence)
33values(1,1,1,1,'2018-09-03',null,'A');
34go
35
36--Trigger definition
37CREATE TRIGGER [dbo].[member_in_test_Modified] ON [dbo].[member_in_test]
38AFTER UPDATE
39AS
40BEGIN
41 SET NOCOUNT ON;
42
43 --Update the rows that were changed to have current date in ToD
44 --We're only interested in rows where the position_id changed and
45 --the original row had credence <> 'W'
46 UPDATE m
47 SET ToD = getdate()
48 FROM dbo.member_in_test m
49 JOIN deleted d ON d.id = m.id
50 JOIN inserted i ON i.id = d.id
51 AND i.position_id <> d.position_id
52 AND d.credence <> 'W'
53
54 --Insert into table all rows from inserted, but make ToD = null
55 INSERT INTO dbo.member_in_test (
56 chart_no
57 ,team_id
58 ,position_id
59 ,member_id
60 ,FromD
61 ,ToD
62 ,credence
63 )
64 SELECT chart_no
65 ,team_id
66 ,position_id
67 ,member_id
68 ,getdate()
69 ,NULL
70 ,credence
71 FROM inserted
72END;
73GO
74
75-----------------------------------------------------------------
76--Verification
77--select data before and after update to verify trigger execution
78select * from dbo.member_in_test;
79go
80update dbo.member_in_test set position_id = 2;
81go
82select * from dbo.member_in_test;
83
84| id | chart_no | team_id | position_id | member_id | FromD | ToD | credence |
85|----|----------|---------|-------------|-----------|-------------------------|------|----------|
86| 1 | 1 | 1 | 1 | 1 | 2018-09-03 00:00:00.000 | NULL | A |
87
88| id | chart_no | team_id | position_id | member_id | FromD | ToD | credence |
89|----|----------|---------|-------------|-----------|-------------------------|-------------------------|----------|
90| 1 | 1 | 1 | 2 | 1 | 2018-09-03 00:00:00.000 | 2018-09-03 06:15:33.740 | A |
91| 2 | 1 | 1 | 2 | 1 | 2018-09-03 06:15:33.740 | NULL | A |