Newbie question: purpose of Delete in Insert trigger 2

[JGresko]

Hi

I have been working with other database for a number of years now, but this is my first experiance with SQL Server. I'm finding a lot of confusing things about the way this database I'm dealing with was written. I'm used to putting my database commands into my application that sits on top of my database, but this one uses triggers very heavly to do just about everything.

The issue I'm dealing with at the moment has to do with finding a Select from Delete in an Insert,Update trigger.

ALTER TRIGGER dbo.subcodes_iutrig
           ON dbo.subcodes
          FOR INSERT, UPDATE
AS 
BEGIN

DECLARE @ACCTNO CHAR(6),
	@ACCTYEAR CHAR(4),
	@DC0BUDGET FLOAT,
	@DEPTID CHAR(4)

SELECT @ACCTNO   = S.Account_no,
       @ACCTYEAR = S.Account_yr,
       @SUBCODE  = S.Subcode,
       @DEPTID   = S.Deptid
FROM Subcodes S, Inserted I
WHERE S.Account_no = I.Account_no
  AND S.Account_yr = I.Account_yr
  AND S.Subcode = I.Subcode
  AND S.D_attrib = I.D_attrib

IF UPDATE(Dc0budget)
    BEGIN

	SELECT @DC0BUDGET = IsNull(Dc0budget,0)
	FROM Inserted

	IF EXISTS(SELECT TOP 1 * FROM Deleted)
	SELECT @DC0BUDGET = @DC0BUDGET - IsNull(Dc0budget,0)
	FROM Deleted


	UPDATE Accounts
	   SET Dc_budget = Dc_budget + @DC0BUDGET
	 WHERE Account_no = @ACCTNO
	  AND Account_yr = @ACCTYEAR
	  AND Deptid = @DEPTID

    END
END

I didn't write this trigger and the original developer is no longer available so I have been asked figure out why it's not doing what it's suppose to. As far as i can see, the Select ... from Deleted is causing the @DC0BUDGET variable to be set to 0 since there is no Deleted data to select from.

Does anyone know why this Select ... from Deleted whould have been put in this trigger? What harm might be done if I simply comment it out to allow the account table to be updated with the correct @DC0BUDGET value?

Thanks,

Judy

 

[vongrunt]

AFAIK "deleted" virtual table is not empty for UPDATE and DELETE...

 

[jmeckley]

it looks like they want to add the difference between the new value and the old value to a column in another table called Accounts.

if you comment out the line then you will add the new value to the accounts table, not the difference between the old and new value in the current table.

Inserted and Deleted tables
If you update a record the trigger can access 2 "special" tables the insert table (new values for the record) and the deleted table (old values being replaced).

Jason Meckley
Database Analyst
WITF

 

[JGresko]

I would expect it to be use in an on Delete trigger. But why is it being used her, in an Insert,Update trigger?

What does it contain during an Update? I'm updating the table with 60 and the
[TT]SELECT @DC0BUDGET = IsNull(Dc0budget,0) FROM Inserted[/TT]
returns 60 as expected, but then the

[TT]IF EXISTS(SELECT TOP 1 * FROM Deleted)
SELECT @DC0BUDGET = @DC0BUDGET - IsNull(Dc0budget,0)
FROM Deleted[/TT]

overwrites it with a 0

Without having to test every conceivable triggering of this triger from the program, I'm trying to find out what damage would be done if I commented out the select from deleted portion.


Sorry, I don't know what AFAIK is?

 

[JGresko]

jmeckley, thanks, that makes more sense. That also answers another question I had about how the adjustment was being made. That explains the why they have it there, so now I just have to debug the logic to get it working correctly.

The original value was 0 so that's where the 0 is coming from. I will do some testing with different values to correct the logic.

many thanks!

--

 

[SQLSister]

The others explained well what the inserted and deleted tables are, so I'm going on to another aspect of triggers that you need to understand.

It is very dangerous to write a trigger such that it only expects to process one record. Your trigger does this and will fail if multiple records are inserted or updated. As a general rule, you do not set the values of field in the inserted or deleted records to a variable and then use the variable in a further insert or update statment as this will only use the last value of the whole set of records.

This is very bad for data integrity and depending on how you wrote it, it may bomb altogether and make the trigger not work. Use joins to the inserted and/or deleted statements instead. Apparently your previous developer was unaware of how to do this. You will need to examine and fix all the triggers in your database to solve this particular problem.

To help you solve your current problem, we would need more information such as what is it doing that is not what you expect it to do. Please give some data on what is inserted or updated and what result you exxpect out and what result you are getting.

Triggers are very hard to debug and fix. What I often do to make it easier to see what is happening is work in query analyzer instead. (If a trigger exists, copy the t-SQL to query analyzer. Drop the existing trigger while you modify and debug the change.) Create two temp tables, @inserted and @deleted. Then fill them with the values I would expect the real deleted and inserted table to have in the instances I'm testing. The put in the code using those temp tables. Then when it fails I can see what line it failed on and get more useful error messages. When I then go to create the trigger, all I have to do is change the references from @deleted to deleted and @inserted to inserted and I know it will work.

In working with triggers, it is particularly important that you work in a development database not production. Triggers can muck up production pretty badly when they don't work causing both data integrity problems or system lockups.



Questions about posting. See faq183-874

 

[JGresko]

Wow, thanks SQLSister for taking the time to outline this information for me.

>>It is very dangerous to write a trigger such that it only expects to process one record.

I follow you there! I did not set this application up and have been called in to help fix it. It looks like I have a lot of work to do. The way this is designed is that when a user clicks save from their application, it updates the main table and sets off triggers like this one to update corresponding tables. Each save passes in the composite primary key for one record only. The only way I could see multiple records being updated at the same time would be from multiple users on different computers updating different accounts at the same time. Since that is always possible, I wonder if SQL server (or the ASP app sitting on top of it, or the webserver itself) is smart enough to queue up requests. There’s a brain drain question a bit out of my league, but that I’ll need to address.

>>Use joins to the inserted and/or deleted statements instead. … You will need to examine and fix all the triggers in your database to solve this particular problem.

Oh fun! But doable. I will pass this on to the others working with me as well to help identify the scope of this issue.

>>To help you solve your current problem, we would need more information such as what is it doing that is not what you expect it to do. Please give some data on what is inserted or updated and what result you expect out and what result you are getting.

Now that I have a better understanding of what’s transpiring in this trigger (thanks to you all) , and your suggestions on how to get more information, I will do a bit more research myself on how this trigger is interacting with the app. If I get lost again I will send more info on the problem. Thanks!

>>In working with triggers, it is particularly important that you work in a development database not production. Triggers can muck up production pretty badly when they don't work causing both data integrity problems or system lockups.

No worries there! I’m on a test system and wouldn’t dream of fiddling around on production!

Very much appreciated SQLSister !!

Thank You !

_

 

[SQLSister]

Differnt users updating simultaneously isn't a considered a multiple record update by SQL Server.

Where you get them is when someone decides to load records to the table from elsewhere (like from production to development or vice versa or when you get a new set of customer records by buying out another company, etc.) or directly thorugh query analyzer (such as when you load test data.) This may not be problem in your system as it stands, but it is amazing how often someone decides to do this for one reason or another and then locks up the whole system. You can probably fix these gradually in the system you described without a problem. But it something that should be looked every time you create or alter a trigger.
I told you that info because that might have been precisely the problem that you had happen.



Questions about posting. See faq183-874

 

[JGresko]

SQLSister,

Understood, and thanks again.

Judy

 

[SQLSister]

Always willing to help another Judy.

Judy

Questions about posting. See faq183-874