CODEWITHSUNDEEP
sqlsql-server-2008
Brandon Stout
Brandon Stout

Reputation: 359

How to create a trigger to allow an insert only if what is being inserted is not in a different table

Ok, I searched for the better part of 2 hours earlier today and was unable to find an answer to my question. So I will attempt to see if you all can help me out.

So what I want to do is create a trigger that on insert into table A will check to see if what is being inserted is in table B, if it is then do NOT insert it into any tables, just ignore the insert, otherwise continue inserting into table A.

For example, [actually very similar to my problem just different names etc]

Say I have Table A and Table B. Table B consists of say "Name" and "Address". Table A contains "Name", "Address", "favorite color", and about 3-4 more misc columns.

I want to insert "Sue", "PO Box 1 Lunar Avenue", "Grey", etc into table A. But low-and-behold there is already a "Sue", "PO Box 1 Lunar Avenue" combination in table B, so i want to ignore that insert and continue.

Neither "Name" or "Address" is a key of any sort, other than there is a constraint on the table that will not allow any "Name" and "Address" combination to be a duplicate.

I am using SQL Server 2008 R2 for a reference point.

Thank you ahead of time for your answers! ^^

Upvotes: 1

Views: 2322

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Something along the lines of:

CREATE TRIGGER T_TableA_I
ON TableA
INSTEAD OF INSERT
AS
     INSERT INTO TableA (Name,Address,/* Other columns */)
     SELECT i.Name,i.Address,/* Other columns from inserted */
     FROM
         inserted i
             left join
         TableB b
             on
                 i.Name = b.Name and
                 i.Address = b.Address
     WHERE
         b.Name is null --So the join was unsuccessful.

But I'm still concerned about whether a later insertion into TabelB needs to be dealt with.

Upvotes: 3

Cory
Cory

Reputation: 12824

Take a look at INSTEAD OF triggers which will allow you to take action before your INSERT occurs:

An INSTEAD OF trigger can take actions such as:

  • Ignoring parts of a batch.
  • Not processing a part of a batch and logging the problem rows.
  • Taking an alternative action when an error condition is encountered.

Upvotes: 0

Related Questions