November 12, 2009 at 6:46 pm
Hi,
May I know how should I write the trigger if I wish to get the inserted record in Table A Server A and insert into Table A Server B?
Thanks.
November 12, 2009 at 10:30 pm
Here you go...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
if that doesnt make any sense to you yet, post again, with you table and column names, will assist you gladly.
regards,
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 12, 2009 at 11:29 pm
CREATE TRIGGER [tr_Emp] ON [Employee]
FOR INSERT
AS
DECLARE
@EmpID varchar(20),
@EmpName varchar(100)
SELECT @EmpID=INSERTED.EmpID, @EmpName=INSERTED.EmpName
FROM INSERTED
BEGIN
SET NOCOUNT ON;
IF @EmpID IS NOT NULL
BEGIN
INSERT INTO [ServerName].[DBName].[Employee] (EmpID, EmpName)
VALUES (@EmpID, @EmpName)
END
END
GO
Is this correct?
November 13, 2009 at 7:46 am
Okay, I have a couple of comments about your situation.
1. Don't do it this way. You do not want a trigger to have to do anything outside the database/server it sits on. You are asking for trouble as the trigger takes place within a transaction. If serverB is unavailable then your trigger will cause the transaction to fail. Since you are using SQL Server 2000 (based on the forum you posted in) I would recommend creating a queue table that you insert the rows into using a trigger and then either a SQL Server job or windows service that processes the data in the queue table and inserts into the table on ServerB. You could also look into replication as it might fit your need (I am not a replication expert).
2. The trigger code you have posted does not handle a set-based insert. So if a set based insert occurs you will not have ALL the rows inserted transferred to the other server.
I also recommend you read this article, Intoduction to DML Triggers[/url]. Then post any questions you might have either on this thread or the discussion thread of the article.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2009 at 10:02 pm
Jack Corbett (11/13/2009)
Okay, I have a couple of comments about your situation.1. Don't do it this way. You do not want a trigger to have to do anything outside the database/server it sits on. You are asking for trouble as the trigger takes place within a transaction. If serverB is unavailable then your trigger will cause the transaction to fail. Since you are using SQL Server 2000 (based on the forum you posted in) I would recommend creating a queue table that you insert the rows into using a trigger and then either a SQL Server job or windows service that processes the data in the queue table and inserts into the table on ServerB. You could also look into replication as it might fit your need (I am not a replication expert).
2. The trigger code you have posted does not handle a set-based insert. So if a set based insert occurs you will not have ALL the rows inserted transferred to the other server.
I also recommend you read this article, Intoduction to DML Triggers[/url]. Then post any questions you might have either on this thread or the discussion thread of the article.
Is SQL Server job means Stored Procedure? Possible to set windows service to process the data in the queue table? Any reference for me?
November 14, 2009 at 9:10 am
A job could call a stored procedure (my preferred method) or it could just be T-SQL.
You'd have to write a windows service, probably use .NET, that reads unprocessed rows in the table, transfers them, then marks them as processed. I don't have a specific places where you can see it done. I've just done similar things many times. Sometimes even more complex where we've uses Microsoft Message Queue as well.
The key is that either of these makes the syncing across servers asynchronous which is what you are looking for. Using a trigger across servers is synchronous so the transaction is made more complex and lengthened, which will hurt concurrency and as mentioned may cause the original transaction to fail, and I don't think you want that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2009 at 4:25 am
Jack Corbett (11/14/2009)
A job could call a stored procedure (my preferred method) or it could just be T-SQL.You'd have to write a windows service, probably use .NET, that reads unprocessed rows in the table, transfers them, then marks them as processed. I don't have a specific places where you can see it done. I've just done similar things many times. Sometimes even more complex where we've uses Microsoft Message Queue as well.
The key is that either of these makes the syncing across servers asynchronous which is what you are looking for. Using a trigger across servers is synchronous so the transaction is made more complex and lengthened, which will hurt concurrency and as mentioned may cause the original transaction to fail, and I don't think you want that.
So means I post the data into table Employee with a flag set to zero. Then I create a stored procedure that get all records with flag = 0 from Employee table and insert into separate server. Then set the stored procedure as a scheduled task right?
November 16, 2009 at 6:14 am
A simple, T-SQL only process would be something like this (not tested so you may have to make some changes):
CREATE TABLE EmployeeQueue
(
EmpId INT,
CreatedDate SMALLDATETIME CONSTRAINT DF_EmployeeQueue_CreatedDate DEFAULT GETDATE(),
ProcessedDate SMALLDATETIME,
CONSTRAINT PK_EmployeeQueue_EmpID_CreatedDate
PRIMARY KEY NONCLUSTERED (EmpId, CreatedDate)
);
GO
CREATE TRIGGER tr_Emp_Ins ON Employee
FOR INSERT
AS
SET NOCOUNT ON;
/* This is now set-based so will handle a set based insert */
INSERT INTO EmployeeQueue (
EmpId
)
SELECT
EmpId
FROM
inserted;
GO
CREATE PROCEDURE EmployeeDataTransfer
AS
SET NOCOUNT ON
DECLARE @emps TABLE (empid INT, createddate);
/*
Need this so we only mark the ones transferred as
processed
*/
INSERT INTO @emps (
empid,
createddate
)
SELECT
EmpId,
CreatedDate
FROM
EmployeeQueue AS EQ
WHERE
ProcessedDate IS NULL;
INSERT INTO LinkedServer.DATABASE.schema.TABLE
SELECT
*
FROM
@emps AS E JOIN
Employee AS EQ ON
E.empid = EQ.EmpId;
/* Successfully transferred so mark as processed */
IF @@ERROR = 0
BEGIN
UPDATE EmployeeQueue
SET ProcessedDate = GETDATE()
WHERE
EXISTS (SELECT 1 FROM @emps AS E WHERE EmpId = E.empid AND createddate = E.CreatedDate);
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 30, 2009 at 7:40 pm
Jack Corbett (11/16/2009)
A simple, T-SQL only process would be something like this (not tested so you may have to make some changes):
CREATE TABLE EmployeeQueue(
EmpId INT,
CreatedDate SMALLDATETIME CONSTRAINT DF_EmployeeQueue_CreatedDate DEFAULT GETDATE(),
ProcessedDate SMALLDATETIME,
CONSTRAINT PK_EmployeeQueue_EmpID_CreatedDate
PRIMARY KEY NONCLUSTERED (EmpId, CreatedDate)
);
GO
CREATE TRIGGER tr_Emp_Ins ON Employee
FOR INSERT
AS
SET NOCOUNT ON;
/* This is now set-based so will handle a set based insert */
INSERT INTO EmployeeQueue (
EmpId
)
SELECT
EmpId
FROM
inserted;
GO
CREATE PROCEDURE EmployeeDataTransfer
AS
SET NOCOUNT ON
DECLARE @emps TABLE (empid INT, createddate);
/*
Need this so we only mark the ones transferred as
processed
*/
INSERT INTO @emps (
empid,
createddate
)
SELECT
EmpId,
CreatedDate
FROM
EmployeeQueue AS EQ
WHERE
ProcessedDate IS NULL;
INSERT INTO LinkedServer.DATABASE.schema.TABLE
SELECT
*
FROM
@emps AS E JOIN
Employee AS EQ ON
E.empid = EQ.EmpId;
/* Successfully transferred so mark as processed */
IF @@ERROR = 0
BEGIN
UPDATE EmployeeQueue
SET ProcessedDate = GETDATE()
WHERE
EXISTS (SELECT 1 FROM @emps AS E WHERE EmpId = E.empid AND createddate = E.CreatedDate);
END
Hi, I follow the code provided above and did some modifications, it works fine.
But just a small part I need clarification:
/* Successfully transferred so mark as processed */
IF @@ERROR = 0
BEGIN
UPDATE EmployeeQueue
SET ProcessedDate = GETDATE()
WHERE
EXISTS (SELECT 1 FROM @emps AS E WHERE EmpId = E.empid AND createddate = E.CreatedDate);
END
This update code will update ALL the ProcessedDate inside the EmployeeQueue, overwrite the ProcessedDate data.
How to modify just to update the processed batch of records?
Is it put "WHERE ProcessedDT IS NULL"?
Please advice. Thanks.
November 30, 2009 at 9:27 pm
Note the WHERE clause on the snippet. I load all the rows with a NULL ProcessedDate into the @emps table variable, process the rows. Then use the table variable to limit the update statement. That's what WHERE EXISTS does.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 30, 2009 at 11:20 pm
Jack Corbett (11/30/2009)
Note the WHERE clause on the snippet. I load all the rows with a NULL ProcessedDate into the @emps table variable, process the rows. Then use the table variable to limit the update statement. That's what WHERE EXISTS does.
DECLARE @emps TABLE (empid INT, createddate);
/*
Need this so we only mark the ones transferred as
processed
*/
INSERT INTO @emps (
empid,
createddate
)
SELECT
EmpId,
CreatedDate
FROM
EmployeeQueue AS EQ
WHERE
ProcessedDate IS NULL;
/*
Testing Code
*/
SELECT *
FROM
EmployeeQueue
WHERE
EXISTS (SELECT 1 FROM @emps AS E WHERE EmpId = E.empid AND createddate = E.CreatedDate);
I try run the above code in SQL Query Analyzer, it returns all the rows inside the EmployeeQueue table.
December 1, 2009 at 5:18 am
If you haven't processed any rows yet then all the rows will be returned.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 1, 2009 at 6:35 pm
Jack Corbett (12/1/2009)
If you haven't processed any rows yet then all the rows will be returned.
Hmm... I still not very clear about that.
A temporarily table "@eps" was created to hold records with NULL ProcessedDate right?
By right the following query should return records with NULL ProcessedDate right?
DECLARE @emps TABLE (empid INT, createddate);
/*
Need this so we only mark the ones transferred as
processed
*/
INSERT INTO @emps (
empid,
createddate
)
SELECT
EmpId,
CreatedDate
FROM
EmployeeQueue AS EQ
WHERE
ProcessedDate IS NULL;
/*
Testing Code
*/
SELECT *
FROM
EmployeeQueue
WHERE
EXISTS (SELECT 1 FROM @emps AS E WHERE EmpId = E.empid AND createddate = E.CreatedDate);
Or I explain in example will be easy to understand:
Table EmployeeQueue:
EmpID CreatedDate ProcessedDate
1 2009-Dec-01 14:00:00 2009-Dec-01 15:00:00
2 2009-Dec-01 16:00:00 NULL
After I run the query stated above, it will return me 2 rows of record. By right it should return me the record with NULL ProcessedDate right?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply