CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
WebDevGuy
WebDevGuy

Reputation: 353

Triggers: Update table2 with info from table1

Using SSMS 2008.

I have 2 tables:

TABLE1:
PKField (Primary Key)
Field2
Field3

TABLE2:
PKField (Primary Key)
FKField (Foreign Key to Table1.PKField)
Field2
Field3

I need to create a trigger on TABLE2 that does this:

Whenever rows are INSERTED into TABLE2 (Field2 and Field3 will always be null at this point) then UPDATE the fields TABLE2.Field2 and TABLE2.Field3 based on TABLE1.Field2 and TABLE2.Field3 values.

Using SSMS 2008 how do I do this? I'm new to triggers, etc...

Thanks!

Upvotes: 0

Views: 113

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Here is a rough sketch based on your example tables.

create trigger MyTrigger on Tabel2 INSTEAD OF INSERT AS
    Insert Table2 (PKField, FKField, Field2, Field3)
    select i.PKField
        , i.FKField
        , t.Field2
        , t.Field3
    from Inserted i
    join Table1 t on t.PKField = i.FKField

Upvotes: 1

gotqn
gotqn

Reputation: 43666

Here you can find more information triggers. I recommend to you to read it first before start doing anything.

In your case, I believe you should create INSTEAD OF INSERT trigger:

INSTEAD OF

Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers. At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view.

For example:

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER myTrigger on myTable
INSTEAD OF INSERT
AS
BEGIN
  -- do whatever you want here
  -- the code is executed when an insertion is made on the underlying table
END;
GO

You can find more information about INSTEAD OF INSERT trigger here.

I guess the only question you will have is how to refer the data that is initially inserted in the underlying table?

The answer is using the inserted table - read more about it

Upvotes: 0

Related Questions