Conditional insert Trigger
-
I have data in a table that I am moving/transforming into other tables. I am strengthening the design of the new tables (creating unique indexes, ref int, foreign keys and other dumb stuff like that). I am new to triggers but I believe they can solve my problem. During the conversion/insert of data from table A to table B, then INSERT INTO… statement fails because a few of the records violate the unique key of table B. Through research, I discovered that these exceptions cannot be deleted or ignored, but need to go into Table C (instead of B). So what I want is a conditional insert.
Normally, table A records go into table B.
When one of the records violate the unique index on table B, (i.e., that field already exists in table B) add that record to table C instead.
I wrote this trigger:
CREATE TRIGGER UIR_NPB_Diversion_Trigger on UIR_Package
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO UIR_Package_Extra_Charges
SELECT TrackingNumber, InvoiceNumber, Transaction_Code, Service_Description, Net_Charges, Incentive
FROM inserted
END
GO
The problem with this one is that every record is getting moved to table C [UIR_Package_Extra_Charges]. I suspect I need an IF statement in here somewhere, but the “IF UPDATE” and “IF COLUMNS_UPDATED” seems to be more related to when the update performs successfully. I want to divert the insert into Table C when the record does not inserted into Table B. The test for this is a single field (TrackingNumber). If TrackingNumber exists in Table B[UIR_Package] then add it to Table C instead. (different table structure)
Functionally, this is what I want to do:
CREATE TRIGGER UIR_NPB_Diversion_Trigger on UIR_Package
FOR INSERT
AS
IF TrackingNumber already exists in TABLE_B_UIR_Package
THEN INSERT INTO TABLE_C_ [...]
ELSE
INSERT INTO TABLE_B [...]
GO
Any help would of course be greatly appreciated.
Mike P.
-
There is something not clear yet. If one of them violates the uniqueness how do you know which goes to B and to C.
In other words if you have
PK =1, Fld1 ='A', Fld2 ='B'
PK=1, Fld1='C', Fld2 = 'D'
Which is going to C and Which is going to B?
If you can answer that question then you probably don't need triggers but two distinct insert commands
* Noel -
I hope this clarifies.
Table A.PK (source table w/duplicates allowed on field PK)
-------------
1
2
3
4
4
Intended results after inserts:
Table B.PK (primary target table w/unique index on field PK)
-------------
1
2
3
4
Table C.PK (alternate target table)
-------------
4
The unique index on field PK of Table B prohibits a second value of ‘4’. I want the record with the second instance of ‘4’ going to Table C.
-
Mike,
There is no such a thing like the first PK or the second PK.
Think about it like the first or the second can only be determined by an order by clause only!
* Noel -
>>I want the record with the second instance of ‘4’ going to Table C.
Yes, but what defines the "second instance" ? Is it arbitrary ? Or based on a sort order of some other column or columns ?
If the PK is duplicated, is there some other combination of column/columns that can be considered 'unique' ?
The answers to these influence whether an efficiant set-based solution is possible, versus using cursored one-record-at-a-time inserting (yuck).
-
Yes, but what defines the "second instance" ? Is it arbitrary ? Or based on a sort order of some other column or columns ?
>>there are other fields which identify the “second instance”. I can solve the situation with two inserts and a delete but the trigger looked much more elegant and efficient in terms of handling the situation. (only 5 records out a million have this condition) I also hoped to “capture” data instances that have yet to show up in the test files. I also hoped to gain some experience with triggers (I’ve got some other triggers to build)
If the PK is duplicated, is there some other combination of column/columns that can be considered 'unique' ?
>> I’m not going there! I am trying to take a single flat file full of no rules into a set of relational tables with integrity and rules. It is duplicate entries like this that I am specifically eliminating. Adding another field to the key would throw the integrity of the single PK field out the window.
The answers to these influence whether an efficiant set-based solution is possible, versus using cursored one-record-at-a-time inserting (yuck).
>>Hmm. Maybe I should ask a different question. I have a primary unique key defined on a table, like all good DBA’s should design. I have the ‘ignore duplicates’ checked off, because I want to know about the records that violate the key. I execute insert commands for data. In one example, the whole insert statement stops and I get the message “Server: Msg 2601, … Cannot insert duplicate key row in object 'tablename' with unique index 'IX_1'. ”. How do I discover the few records that violate the key? (in this case, the 5 of a million) Is there a better way to do this? (discover records that violate a key during an insert)
-
Find the "bad" records:
Select *
From TableA
Where PK In (
Select PK
From TableA
Group by PK
Having Count(*) > 1
)
Write 'Safe" Insert that will not error due to dupes:
Insert Into TableB
(<Column List>
Select
<Column List>
From TableA
Where PK Not In (
Select PK
From TableA
Group by PK
Having Count(*) > 1
)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply