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.
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
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.
Code:
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