· 8 years ago · Dec 14, 2017, 06:50 AM
1select m_uid from EmpTaxAudit
2where clientid = 91682
3and empuid = 42100176452603
4and newvalue in('Deleted','DB-Deleted','Added')
5
6CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].
7
8[EmpTaxAudit]
9(
10 [ClientID] ASC,
11 [NewValue] ASC
12)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
13
14ALTER TABLE [dbo].[EmpTaxAudit] ADD CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED
15(
16 [ClientID] ASC,
17 [EmpUID] ASC,
18 [m_uid] ASC,
19 [m_eff_start_date] ASC,
20 [ReplacedOn] ASC,
21 [ColumnName] ASC
22)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
23
24CREATE trigger [dbo].[trins_EmpTax]
25on [dbo].[emptax]
26for insert
27as
28begin
29 declare
30 @intRowCount int,
31 @user varchar(30)
32
33 select @intRowCount = @@RowCount
34 IF @intRowCount > 0
35 begin
36 select @user = suser_sname()
37
38 insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
39 select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
40 from inserted i
41 where m_uid not in (select m_uid from EmpTaxAudit
42 where clientid = i.clientid and (newvalue = 'Deleted'
43 or newvalue = 'DB-Deleted'
44 or newvalue = 'Added') and empuid = i.empuid)
45 and i.m_eff_end_date is null
46
47 insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
48 select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
49 from inserted i
50 where m_uid not in (select m_uid from EmpTaxAudit
51 where clientid = i.clientid and (newvalue = 'Deleted'
52 or newvalue = 'DB-Deleted'
53 or newvalue = 'Added') and empuid = i.empuid)
54 and i.m_eff_end_date is not null
55
56
57 end
58end
59
60create unique index IX_EmpTaxAudit(
61 [ClientID] ASC,
62 [EmpUID] ASC,
63 [m_uid] ASC,
64 [m_eff_start_date] ASC,
65 [ReplacedOn] ASC,
66 [ColumnName] ASC
67)
68include (newvalue)
69
70CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].
71
72 [EmpTaxAudit]
73 (
74 [ClientID] ASC,
75 [NewValue] ASC,
76 [newvalue]
77 )
78 INCLUDE (m_uid)
79 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
80
81CREATE trigger [dbo].[trins_EmpTax]
82on [dbo].[emptax]
83for insert
84as
85begin
86
87if @@rowcount = 0
88 return
89 set nocount on
90
91 declare
92 @intRowCount int,
93 @user varchar(30)
94
95 --select @intRowCount = @@RowCount
96 if exists(select * from inserted)
97 begin
98 select @user = suser_sname()
99
100 insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
101 select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15)
102 ,case when i.m_eff_end_date is null then 'Added' else 'Deleted' END
103 from inserted i
104 where not exists (select m_uid from EmpTaxAudit ETA
105 where i.m_uid=ETA.m_uid and empuid = i.empuid and clientid = i.clientid
106 and newvalue in('Deleted','DB-Deleted' , 'Added')
107 )
108
109
110 --insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
111 --select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
112 -- from inserted i
113 -- where m_uid not in (select m_uid from EmpTaxAudit
114 -- where clientid = i.clientid and (newvalue = 'Deleted'
115 -- or newvalue = 'DB-Deleted'
116 -- or newvalue = 'Added') and empuid = i.empuid)
117 -- and i.m_eff_end_date is not null
118
119
120 end
121end