CODEWITHSUNDEEP
sqlsql-servertriggers
Khaled Zaid
Khaled Zaid

Reputation: 13

Need a Trigger to Copy New row to other table With some conditions

I have 2 tables (A,B).

I need trigger to do this :

Copy a New row inserted to table A and field (3) > 1 and field (7)!='' To table B.

Note : there are huge data inserts to table A (at peak times) for that need code not effect performance.

Upvotes: 0

Views: 30

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

In SQL Server, I would phrase this as;

create trigger trig_tableA_insert on tableA after insert
as begin    
    insert into tableB (Field1, Field2, Field3, Field6, Field7)
        select i.Field1, i.Field2, i.Field3, i.Field6, i.Field7 
        from inserted i
        where i.field3 > 1 and i.field7 <> '';
end;

This is safe for statements that insert multiple rows at one time.

If you have lots of inserts, I wonder if this is the best approach. Presumably, you need the data right away in tableB, because triggers incur overhead.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

here's your trigger. adding below condition will not insert data to your tableB.

create trigger [t_TableA]
on tableA
after  [insert]
as
begin

    if ((select 1 from inserted where field3 > 1 and coalesce(field7, '') != '') != 1)
        begin
            return;
        end

    insert into tableB (Field1, Field2, Field3, Field6, Field7)
    select Field1, Field2, Field3, Field6, Field7 from inserted
end

Upvotes: 0

Related Questions