CODEWITHSUNDEEP
sql-servert-sql
Adrian Walker
Adrian Walker

Reputation: 23

Copy from one table in a database, to an exact copy of the table on another database on the same SQL server using a trigger

Fairly new to SQL. I am trying to copy a tables data from one database to a table exactly the same on another database within the same SQL Server instance using an on insert trigger.

If I run it as a standalone query:

SET NOCOUNT ON;

DECLARE @IDTSMAX AS INT 

SET @IDTSMAX = (SELECT MAX(IDTS)
                FROM [LabReports].[dbo].[TruckQualityLAB])

INSERT INTO [DryPlant].[dbo].[TruckQualityLAB] (BOL, Quality, Location, Product, Date, IDTS)
    SELECT 
        BOL, Quality, Location, Product, Date, IDTS 
    FROM 
        [LabReports].[dbo].[TruckQualityLAB] 
    WHERE
        @IDTSMAX = IDTS

it works just fine, but if I create it as a trigger, it fails and the insert to either table doesn't occur. The primary table on LabReports is populated by 8 insert triggers from other tables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TruckQualityLABUPDATE] 
ON [LabReports].[dbo].[TruckQualityLAB]
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @IDTSMAX AS INT
    SET @IDTSMAX = (SELECT MAX(IDTS)
                    FROM [LabReports].[dbo].TruckQualityLAB])

    INSERT INTO [DryPlant].[dbo].[TruckQualityLAB](BOL, Quality, Location, Product, Date, IDTS)
        SELECT 
            BOL, Quality, Location, Product, Date, IDTS 
        FROM 
            [LabReports].[dbo].[TruckQualityLAB] 
        WHERE
            @IDTSMAX = IDTS
END

I need this data copied so I can then update truck records with correct quality for reporting etc.

Any ideas why this is not working as a trigger? Everything I read says it should work.

EDIT. The TruckQualityLAB table is updated randomly, from 2 different labs using a web interface, so it is possible for 2 or 3 records to be entered within seconds of each other, since this table is populated from insert triggers on 8 other product tables.( On Insert create row in truckqualitylab table). I then want to use this consolidated table in the other DB to update records with quality values. These have to be LIVE for the web pages to report on demand.

Upvotes: 2

Views: 94

Answers (1)

marc_s
marc_s

Reputation: 754408

You need to check out the Inserted pseudo table - this contains the newly inserted rows - these need to be stored to the other table

ALTER TRIGGER [dbo].[TruckQualityLABUPDATE] 
ON [LabReports].[dbo].[TruckQualityLAB]
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [DryPlant].[dbo].[TruckQualityLAB](BOL, Quality, Location, Product, Date, IDTS)
        SELECT 
            BOL, Quality, Location, Product, Date, IDTS 
        FROM 
            Inserted
END

Upvotes: 1

Related Questions