SQL Server 2005 Trigger Problem

Question

Hi ,

I have got an "A" DATABASE and "B" DATABASE.When i inserted new record to "X TABLE" in "A" DATABASE i wanna insert another record to "X2 Table" in "B" DATABASE with Trigger Function of SQL SERVER 2005.How can i do this ? Can someone explain me this. How can i connect another Database and insert record specific table.

Thanx everyone.

Answer 1

Im not sure if it's doable in triggers, but in stored procedures you would write

INSERT INTO DBNAME.SCHEMA.TABLENAME(...)
VALUES(...

So, probably something like

INSERT INTO B.dbo.X2(...

Answer 2

Is the database in the same sql server instance? if so, you can interact with tables in a neighboring database using <database name>.<schema name>.<table name>. If the database is in a separate instance, you would have to create a Linked Server and access through that.

Answer 3

The only problem that you will have is in qualifying the name of the new database. Otherwise, it's like a trigger writing to the same database. Here is an example of the T-SQL portion:

INSERT INTO b.dbo.X2
SELECT * FROM Inserted

Answer 4

Here is a complete, working example.

Code

USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'A')
    DROP DATABASE A
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'B')
    DROP DATABASE B
GO
CREATE DATABASE A
GO
CREATE DATABASE B
GO
USE A
GO
CREATE TABLE X (Col1 varchar(10) NOT NULL)
GO
USE B
GO
CREATE TABLE X2 (Col1 varchar(10) NOT NULL)
GO
USE A
GO
CREATE TRIGGER dbo.trX_Insert
ON dbo.X
FOR INSERT
AS BEGIN
    INSERT INTO B.dbo.X2 (Col1)
    SELECT Col1
    FROM Inserted
END
GO
INSERT INTO A.dbo.X (Col1) VALUES ('This')
INSERT INTO A.dbo.X (Col1) VALUES ('That')
GO
SELECT * FROM A.dbo.X
SELECT * FROM B.dbo.X2
GO

Result

Col1
----------
This
That

Col1
----------
This
That