SQL Trigger | Inserting multiple entries

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
LVL 12
Muhammad BurhanManager I.T.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the Primary Key for [HR_TimeOffice] table?
0
ste5anSenior DeveloperCommented:
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.
1
Muhammad BurhanManager I.T.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.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Muhammad BurhanManager I.T.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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Muhammad BurhanManager I.T.Author Commented:
does openquery will work ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, OPENQUERY should work. Is a workaround for who don't want to create permanent linked servers.
0
Muhammad BurhanManager I.T.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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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. "
0
Muhammad BurhanManager I.T.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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Muhammad BurhanManager I.T.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'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Told you so ;)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, ste5an. Didn't realize you already told that or I would mentioned your comment instead.
0
Muhammad BurhanManager I.T.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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
for the LINKED SERVER issue, can you script it and post here the creation script?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Muhammad, a feedback will be appreciated.
0
Muhammad BurhanManager I.T.Author Commented:
i will post that script by tommorrow
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still waiting.
0
Muhammad BurhanManager I.T.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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's why you should refer to it with the LINKED server name and not HR because it will always search locally first.
0
Muhammad BurhanManager I.T.Author Commented:
yes! when linked server's name is used before DB 'H.R' nothing replicated on trigger nor any error occur.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
for the LINKED SERVER issue, can you script it and post here the creation script?
0
Muhammad BurhanManager I.T.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'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.