Avatar of Muhammad Burhan
Muhammad Burhan
Flag for Pakistan asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
Muhammad Burhan

8/22/2022 - Mon
Vitor Montalvão

What's the Primary Key for [HR_TimeOffice] table?
SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Muhammad Burhan

ASKER
@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.
Muhammad Burhan

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vitor Montalvão

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 Burhan

ASKER
does openquery will work ?
Vitor Montalvão

Yes, OPENQUERY should work. Is a workaround for who don't want to create permanent linked servers.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Muhammad Burhan

ASKER
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ão

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 Burhan

ASKER
'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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

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 Burhan

ASKER
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'.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

Told you so ;)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

Sorry, ste5an. Didn't realize you already told that or I would mentioned your comment instead.
Muhammad Burhan

ASKER
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ão

for the LINKED SERVER issue, can you script it and post here the creation script?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

Muhammad, a feedback will be appreciated.
Muhammad Burhan

ASKER
i will post that script by tommorrow
Vitor Montalvão

Still waiting.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Muhammad Burhan

ASKER
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ão

That's why you should refer to it with the LINKED server name and not HR because it will always search locally first.
Muhammad Burhan

ASKER
yes! when linked server's name is used before DB 'H.R' nothing replicated on trigger nor any error occur.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

for the LINKED SERVER issue, can you script it and post here the creation script?
Muhammad Burhan

ASKER
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'