asked
T-SQL TRIGGER FAKE UPDATE ENTRY
HI EVERYONE !!! **CAN WE PREVENT THE FAKE UPDATE ENTRY OF TRIGGER?** MY PROBLEM IS AS FOLLOW: I HAVE ONE TABLE CALLED POLMAIN. ON THIS POLMAIN TABLE THERE ARE THRE TRIGGER(INSERT,UPDATE,DELETE).THEY ARE AS FOLLOWS: 1. rtp_inspolmain(insert trigger) 2. rtg_updpolmain(upadte trigger) 3. rtg_delpolmain(delete trigger) IF ANY FIELD OF POLMAIN TABLE GETS UPDATE THEN THE UPDATE TRIGGER WILL EXECUTE AND MAKES AN UPDATE ENTRY IN TABLE INTPOLMAIN.(THIS IS NORMAL PROCESS). BUT IF THERE'S NO CHANGE IN TABLE POLMAIN OR WE CAN SAY IF TABLE POLMAIN GET UPDATE WITH SAME RECORD,THIS UPDATE TRIGGER INSERT A FAKE ENTRY IN TABLE INTPOLMAIN. I WANT TO STOP THIS FAKE UPDATE ENTRY IN TABLE INTPOLMAIN. PLEASE PROVIDE YOUR VALUABLE SUGGESTION ON THIS. IT WILL BE APPRICIABLE!!! CHEERS, SHALABH
@shalabh21 Suppose your table named **POLMAIN** has 1000 records. If you issue a statement like this: update POLMAIN set [SomeColumn] = [SomeColumn] which is essentially a faux update against every record in the table then you should see 1000 new records in your **INTPOLMAIN** audit table. This is because as far as the SQL Server is concerned, the update statement affected every record in the table despite the fact that the update did not really change any values (thus the **faux update** name). How does your update trigger handle this situation? I hope you don't have any cursor in it looping through the records in the inserted logical table and then issuing an insert of a single record into INTPOLMAIN table from the values fetched from the currrent cursor's record. You probably have something like this instead: insert into INTPOLMAIN select {some_columns} from inserted; Please post an abridged version of your trigger, so someone can post a sample solution, but please don't use ALL CAPS thingy, it is pretty irritating to see the questions posted like this.
I see some problems in this trigger. It definitely has too many responsibilities, but if your business requirements dictate it, the complexity cannot really be reduced. One line of code which looks suspicious is this one: select @UnitPsu = UnitPsu, @PolId = PolId from Inserted This is because if your update statement affects multiple records then the line above ends up setting the @UnitPsu and @PolId to the values of the UnitPsu and PolId from the ***last record*** in the inserted table (trigger fires once per update statement not once per record). I am not sure whether this is a desired behaviour or not. Another small problem is the usage of the older syntax. I would definitely consider replacing the part from Inserted i ,Deleted d where i.PolId = d.PolId and i.UnitPsu = d.UnitPsu with its standard equivalent from inserted i inner join deleted d on i.PolId = d.PolId and i.UnitPsu = d.UnitPsu Finally, the actual question is about preventing the insert into IntPolMain table records with Actn = 'U' in case if the update did not actually change any values in the table (first insert in your trigger) For example, suppose you issue a faux update like this: update POLMAIN set PolId = PolId; This is clearly a useless faux update but the first insert in your trigger will cause inserting into IntPolMain as many records as there are in your POLMAIN table. This means that you have to modify your first insert from its current version reading insert into IntPolMain select i.PolId, i.UnitPsu, 'U', @Dt from inserted i where exists ( /* etc */) to something different. One way of doing it is to involve a join of deleted and inserted logical records and checking the values in every column (yes, one by one) in deleted and inserted to allow insert only if any are different. Lets, for example, say that your POLMAIN table has 3 more columns named ColumnA, ColumnB and ColumnC and, to increase complexity, assume that every column is nullable. This will make your first insert look something like this: insert into IntPolMain (PolId, UnitPsu, Actn, Dt) select i.PolId, i.UnitPsu, 'U', @Dt from inserted i inner join deleted d on i.PolId = d.PolId and i.UnitPsu = d.UnitPsu where exists ( select 1 from IntPolMain P where P.PolId = i.PolId and P.UnitPsu = i.UnitPsu and P.Actn = 'I' ) and ( -- ColumnA change detection (d.ColumnA is null and i.ColumnA is not null) -- change from null or (d.ColumnA is not null and i.ColumnA is null) -- change to null or (d.ColumnA i.ColumnA) change of not null value -- ColumnB change detection or (d.ColumnB is null and i.ColumnB is not null) -- change from null or (d.ColumnB is not null and i.ColumnB is null) -- change to null or (d.ColumnB i.ColumnB) change of not null value -- ColumnC change detection or (d.ColumnC is null and i.ColumnC is not null) -- change from null or (d.ColumnC is not null and i.ColumnC is null) -- change to null or (d.ColumnC i.ColumnC) change of not null value ) This makes the where clause look pretty messy but it should work just fine. Of course if your other columns are not nullable then the logic does not have to be this complex as the check for every column change will be reduced to a simple **or (d.ColumnX i.ColumnX)** Oleg
