SQL Trigger | Inserting multiple entries

Muhammad Burhan
Muhammad Burhan used Ask the Experts™
on
We have two Databases, Source -> 'HR_TimeOffice' and Dest -> 'HR'.
we have to create a trigger which just replicate new entries automatically from sources DB's table to destination DB's table.

Begin
INSERT INTO [HR].[dbo].[EmpAttendance] 
			(empcode,reflectingdate,datetime,inout,ismanual,entryno)
SELECT     RIGHT(REPLICATE(0,5)+[HR_TimeOffice].DBO.EmpAttendance.EmpCode ,5) EMPLOYEENO,
           CONVERT(CHAR(8), i.DateTime, 112),i.DateTime,
           case when (i.InOut='I') then 'IN' else 'OUT' End,'0',null
           FROM inserted i
           	 INNER JOIN [HR_TimeOffice].dbo.EmpAttendance ON
	     EmpAttendance.EmpCode  = i.EmpCode
		 END

Open in new window


the trigger did its job as it should, but it insert multiple entries with same data in destination table; *review attachments.
Also please advice if we want to replicate those entries in the database which is hosted by another server 'Server2' with same DB name 'HR' and same tables. Can we use 'Linked Server' feature to do this ?
both server are in the same domain as well.
Source.jpg
Dest.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What's the Primary Key for [HR_TimeOffice] table?
ste5anSenior Developer
Commented:
Why using that JOIN in the trigger? This is the reason for your duplicates. As far as I understand your trigger, this should do it:

INSERT  INTO [HR].[dbo].[EmpAttendance]
        ( empcode ,
          reflectingdate ,
          datetime ,
          inout ,
          ismanual ,
          entryno
        )
        SELECT  RIGHT(REPLICATE(0, 5) + i.EmpCode, 5) ,
                CONVERT(CHAR(8), i.DateTime, 112) ,
                i.DateTime ,
                CASE WHEN ( i.InOut = 'I' ) THEN 'IN'
                     ELSE 'OUT'
                END ,
                '0' ,
                NULL
        FROM    inserted i;

Open in new window


And yes, you can use a linked server to copy the data to your second server.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
@vitor,
the PK is EmpCode

@ste5an,
will try that adjustment and will post the results, also please write a little bit more regarding procedure/method to use linked server feature for our requirement.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
with little adjustment in above suggestion worked, but what will be the query for trigger to insert / replicate table's data into linked server's db ?
linked server for 'SQLSERVER2' will be 'SQLSERVER' having DB name HR and
[SQLSERVER].[HR].[dbo].[EmpAttendance]

Open in new window

successfully completed, but nothing replicated.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Just realized that I meant [HR_TimeOffice].dbo.EmpAttendance table and not [HR_TimeOffice] since that's the database name.

linked server for 'SQLSERVER2' will be 'SQLSERVER' having DB name HR and
Isn't the trigger being fired in HR database side? So the linked server should be used for the SQL Server instance where [HR_TimeOffice] database is stored.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
does openquery will work ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, OPENQUERY should work. Is a workaround for who don't want to create permanent linked servers.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
adding linked server permanently is not an issue here, after adding linked server 'sqlserver' the query
insert into [SQLSERVER].[HR].[dbo].[EmpAttendance]

Open in new window

"didn't work.
any idea ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
any idea ?
I've asked this before but you didn't answer me yet:
"Isn't the trigger being fired in HR database side? So the linked server should be used for the SQL Server instance where [HR_TimeOffice] database is stored. "
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
'HR_TimeOffice' is the DB name which is hosted by 'SQLSERVER2', we linked 'SQLSERVER' as linked server in which the destination DB 'HR' is hosted.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
we linked 'SQLSERVER' as linked server in which the destination DB 'HR' is hosted.
Shouldn't you link SQLSERVER2 instead? So your INSERT will look like (check the INNER JOIN clause):
INSERT INTO [HR].[dbo].[EmpAttendance] 
			(empcode,reflectingdate,datetime,inout,ismanual,entryno)
SELECT  RIGHT(REPLICATE(0,5)+[HR_TimeOffice].DBO.EmpAttendance.EmpCode ,5) EMPLOYEENO,
           CONVERT(CHAR(8), i.DateTime, 112),i.DateTime,
           case when (i.InOut='I') then 'IN' else 'OUT' End,'0',null
FROM inserted i
	 INNER JOIN SERVER2.[HR_TimeOffice].dbo.EmpAttendance ON  EmpAttendance.EmpCode  = i.EmpCode

Open in new window

Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
entries form application comes at 'SQLSERVER2' in db 'HR_TimeOffice' in 'EmpAttendance' table. We linked 'SQLSERVER' in 'SQLSERVER2'. So the destination for trigger will be 'SQLSERVER' and the DB will be 'HR' then the table will be 'EmpAttendance'.
IT Engineer
Distinguished Expert 2017
Commented:
entries form application comes at 'SQLSERVER2' in db 'HR_TimeOffice' in 'EmpAttendance' table
Ok. I think I already realized what confused me. You don't need at all the INNER JOIN:
INSERT INTO [HR].[dbo].[EmpAttendance] 
	(empcode,reflectingdate,datetime,inout,ismanual,entryno)
SELECT RIGHT(REPLICATE(0,5)+i.EmpCode ,5) EMPLOYEENO,
       CONVERT(CHAR(8), i.DateTime, 112),i.DateTime,
       case when (i.InOut='I') then 'IN' else 'OUT' End,'0',null
FROM inserted i

Open in new window

Now, for the LINKED SERVER issue, can you script it and post here the creation script?
ste5anSenior Developer

Commented:
Told you so ;)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sorry, ste5an. Didn't realize you already told that or I would mentioned your comment instead.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
brothers, DB 'HR' is hosted at linked server. Ste5an's suggestion worked only for local DB and I already acknowledge that. What we need is to insert that EmpAttendance table's data into Linked server's DB 'HR' in table 'EmpAttendance'.
so when we try this
insert into [SQLSERVER].[HR].[dbo].[EmpAttendance]

Open in new window

in the trigger, it didn't replicate anything there.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
for the LINKED SERVER issue, can you script it and post here the creation script?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Muhammad, a feedback will be appreciated.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
i will post that script by tommorrow
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Still waiting.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
Vitor, the DB 'HR' is hosted in both of the servers for different purposes. If suggested line
INSERT INTO [HR].[dbo].[EmpAttendance]

Open in new window

is used, the server consider its own hosted DB as destination not linked server's.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
That's why you should refer to it with the LINKED server name and not HR because it will always search locally first.
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
yes! when linked server's name is used before DB 'H.R' nothing replicated on trigger nor any error occur.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
for the LINKED SERVER issue, can you script it and post here the creation script?
Muhammad BurhanManager I.T.
Top Expert 2015

Author

Commented:
On server 'sqlserver2' with linked 'sqlserver',
USE [HR_TimeOffice]

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Trigger] ON [dbo].[Emp_Attendance]
FOR INSERT, UPDATE 
AS 

INSERT INTO [sqlserver].[HR_TimeOffice].[dbo].[EmpAttendance] 
			(empcode,reflectingdate,datetime,inout,ismanual,entryno,computername,trdate,pcname)
SELECT     RIGHT(REPLICATE(0,5)+[HR_TimeOffice].DBO.USERINFO.BADGENUMBER ,5) EMPLOYEENO,
           CONVERT(CHAR(8), i.CHECKTIME, 112),i.CHECKTIME,
           case when (i.CHECKTYPE='I') then 'IN' else 'OUT' End,'0',null,HOST_NAME(),GETDATE(),HOST_NAME()
           FROM inserted i
           	 INNER JOIN [HR_TimeOffice].dbo.USERINFO ON
	     USERINFO.USERID  = i.userid

Open in new window

Both of the servers have same DB with same name 'HR_TimeOffice' for different purposes, requirement is to replicate all entries when they came on 'SQLSERVER2' DB's table 'Emp_Attendance' to 'SQLSERVER' DB's table 'Emp_Attendance'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial