Link to home
Start Free TrialLog in
Avatar of Muhammad Burhan
Muhammad BurhanFlag 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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What's the Primary Key for [HR_TimeOffice] table?
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.
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.
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.
does openquery will work ?
Yes, OPENQUERY should work. Is a workaround for who don't want to create permanent linked servers.
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 ?
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. "
'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.
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

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Told you so ;)
Sorry, ste5an. Didn't realize you already told that or I would mentioned your comment instead.
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.
for the LINKED SERVER issue, can you script it and post here the creation script?
Muhammad, a feedback will be appreciated.
i will post that script by tommorrow
Still waiting.
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.
That's why you should refer to it with the LINKED server name and not HR because it will always search locally first.
yes! when linked server's name is used before DB 'H.R' nothing replicated on trigger nor any error occur.
for the LINKED SERVER issue, can you script it and post here the creation script?
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'