Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
btrimpop
Posting Yak Master
214 Posts |
 Posted - 2002-01-29 : 11:56:13
|
| I've been playing devil's advocate here are work and the discussion has gotten hot. The question is:Do you think the insert trigger on a table should fire if the insert statement returns 0 rows? If yes why? If no why?I've taken the stance, that sure why not, perhaps you need to log the fact that nothing was inserted or you need to roll back cascaded inserts. The counter-point argument is saying that the logic does not belong in the insert trigger it should be captured elsewhere, an insert trigger should only fire if there is something to insert.What do you think?"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-01-29 : 12:05:11
|
| I would think it depends on what your trigger does. If it facilitates an audit trail then I would want it to fire regardless of whether the operation failed or not. I would want to know if somebody was trying to fiddle with my data. Another example, if you have a nightly batch upload you would want to capture the success of the entire process even if there were no records inserted. m2c,Justin |
 |
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2002-01-29 : 12:23:11
|
| True, but again playing devil's advocate. Shouldn't some other mechanism be used to capture this info. Whether you put the logic to check if rows are going to be or were insert in a pre-insert process or post-insert process. The insert trigger should only fire if there is data to insert. Let's put it in OOP terms. Let's say we have a Window control, and we try to issue a SetFocus to it. If setting focus fails an OnEnter event attached to the control does not fire because we could not enter the control. Using a the same paradigm, if I issue an insert command and there is nothing to insert, why should the insert trigger event fire?"In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-29 : 13:11:25
|
quote:
If it facilitates an audit trail then I would want it to fire regardless of whether the operation failed or not. I would want to know if somebody was trying to fiddle with my data.
I totally agree with Justin on this point. An audit trail is looking for activity as well as content...at least most audit trails are constructed with this in mind. You'd want a burgular alarm to go off even if the burgular didn't actually get into the house, right?Now, you don't have to necessarily put this logic in a trigger, BUT you get a free ride by doing so. Regardless of how the INSERT is done, the trigger will catch it and handle it accordingly.Also, suppose the trigger itself does some consistency/data checking and determines these rows SHOULD NOT be inserted and then rolls back the insert? If the external insert process tries to put in 100 rows and the trigger rolls them back, now there's a huge potential for data inconsistency. Don't know if you're doing something like this, but it's a consideration.IMHO if there is some logic that must be applied to INSERT or other operations, it's best and most easily done through the trigger. And if you're doing INSERTs through stored procedures, you can always have both worlds: keep the trigger in place but add checking to the SP, and it can always skip the INSERT if the data fails its own testing. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-29 : 13:13:43
|
| Both points of view are equally valid.An insert trigger sounds like a trigger that copes with an insert operation.A trigger for insert sounds more like a trigger that copes with inserted rows.Also any locks taken are held for the duration of the trigger and the transaction log entry remains blocked. If no rows are inserted then these may not be as expected.It's one of the things that you probably should not rely on happenning or not happenning.A better question might be.Should an after trigger fire if the instead of trigger doesn't insert any rows - is an instead of insert trigger inserting no rows the same as it not inserting.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
For discussion