CODEWITHSUNDEEP
sqlsql-servertriggers
whoisearth
whoisearth

Reputation: 4170

sql server - trigger to copy rows from 2 tables

I have a trigger that is called when there's an Update, Insert or Delete on a table that is joined to another table by a PK/FK 1 to 1 relationship.

Currently I'm copying rowX from TableA when a U, I or D occurs. I want it to also copy rowX from TableB at the same time.

How to I do this?

USE [Database]
GO
/****** Object:  Trigger [dbo].[archiveTable]    Script Date: 14/01/2014 3:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[archiveTable] ON [dbo].[TableA]
   AFTER INSERT,UPDATE,DELETE
AS

DECLARE @HistoryType    char(1) --"I"=insert, "U"=update, "D"=delete
DECLARE @Id INT

SET @HistoryType=NULL
SET @Id=NULL

IF EXISTS (SELECT * FROM INSERTED)
BEGIN
    IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        --UPDATE
        SET @HistoryType='U'
    END
    ELSE
    BEGIN
        --INSERT
        SET @HistoryType='I'
    END
    --handle insert or update data
    INSERT INTO [database2].[dbo].[tableA]
            (column1, column2, ...)
        SET @Id=INSERTED.column5
        SELECT
            GETDATE(), @HistoryType,
            column1, column2, ...
            FROM INSERTED
            JOIN tableB ON INSERTED.column5 = table5.column1
    INSERT INTO [database2].[dbo].[tableB]
            (column1, column2, column3, ...)
        SELECT
            GETDATE(), @HistoryType,
            column1, column2, column3, ....
            FROM jobdtl WHERE column1 = INSERTED.column5


END
ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
    --DELETE
    SET @HistoryType='D'

same as above except for delete

END

I'm guessing I need an inner join somewhere or do I need a variable to get the @job_id so it knows to copy the relevant info from the second table?

edit - from the looks of it I need to somehow use SCOPE_IDENTITY() however it's not taking the job_id of the transaction it's taking the actual ID of the transaction (ie. 1, 2, 3, etc. whereas I need it to be dynamic as job_id may be 54, 634, 325, etc.)

Upvotes: 0

Views: 233

Answers (2)

HarshSharma
HarshSharma

Reputation: 660

I think, you can use the UNION clause to insert two rows from different table at one time. I have made a demo query you just need to change it to your query.

create table #a
(
    id int,
    name varchar(10)
)
insert into #a
select 1,'ax'
union
select 1,'bx'

select * from #a
drop table #a

This is just the sample query with the logic, and i hope you understand it.

Upvotes: 0

Thejaka Maldeniya
Thejaka Maldeniya

Reputation: 1076

To run multiple statements in a trigger, enclose the statements in a BEGIN END block.

You may define multiple triggers for a particular event.

To refer to inserted/updated data, use "inserted" pseudo table. For deleted rows, use "deleted" pseudo table.

It's not clear from your question whether you want to insert into TableA/TableB or if these are the tables for which the triggers are defined. (The SELECT statement lacks a from clause)

Upvotes: 1

Related Questions