Need help on a Trigger
-
I have too little experience writing Triggers and I need some direction.
I have a simple Function which multiplies a Dollar amount times a percentage [ dbo.DollarsUpdate ]. Basically, I know I can get the ID value I need from "inserted" during an Insert, but what about an Update?
Also, I have this sectioned out for an Insert and an Update of the particular field of interest [ Dollars ]. Is that the best way to handle this or should I just do a one fell swoop update regardless of which field is altered?
TIA.
------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Dollars]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[Dollars]
GO
CREATE TRIGGER Dollars
ON dbo.TableName
AFTER INSERT, UPDATE
AS
DECLARE @ID integer,
@TextValue varchar(4),
@Fiscal_Year char(4)
-- IF INSERT( Dollars) -- Errors here...
BEGIN
SELECT @ID = (SELECT [ID] FROM inserted)
SELECT @TextValue = (SELECT TextValue FROM TableName WHERE [ID] = @ID)
SELECT @Fiscal_Year = (SELECT m.Fiscal_Year FROM AnotherTable m INNER JOIN TableName a ON( m.OtherID = a.OtherID) WHERE [ID] = @ID)
BEGIN TRANSACTION InsertDollars
UPDATE TableName SET
Dollars = dbo.DollarsUpdate( @ID, @TextValue, @Fiscal_Year)
WHERE [ID] = @ID
COMMIT TRANSACTION InsertDollars
END
IF UPDATE( Dollars)
BEGIN
SELECT @ID = (SELECT [ID] FROM inserted)
SELECT @TextValue = (SELECT TextValue FROM TableName WHERE [ID] = @ID)
SELECT @Fiscal_Year = (SELECT m.Fiscal_Year FROM AnotherTalbe m INNER JOIN TableName a ON( m.OtherID = a.OtherID) WHERE [ID] = @ID)
BEGIN TRANSACTION UpdateDollars
UPDATE TableName SET
Dollars = dbo.DollarsUpdate( @ID, @TextValue, @Fiscal_Year)
WHERE [ID] = @ID
COMMIT TRANSACTION UpdateDollars
END
I wasn't born stupid - I had to study.
-
Hey Bull, how you doing
.Inserted contains the value when an insert is done and when an update is done (contains new values)
Deleted keeps the values of deleted rows and the old values of and update.
Those are TABLES. Join those tables back to the main table so that you take action only on modified rows, and update everything at once.
Also why are you not using calculated fields in this case??
-
I have tried using both "inserted" and "deleted" for the update section of this Trigger and neither changes the column [Dollars].
I must be doing something wrong, but I cannot see what it might be... Nor am I sure how to test a Trigger.
(Not sure what you mean by "calculated fields". Dollar is a calculated field... :blush
I wasn't born stupid - I had to study.
-
Farrell,
You are better off separating this into two triggers one for insert and one for update. IF UPDATE() is meant for UPDATE Triggers. The trigger should ALWAYS be coded to handle MULTIPLE rows.
Some examples:
CREATE TRIGGER Dollars
ON dbo.TableName
AFTER UPDATE
AS
if @@rowcount = 0 Return
SET NOCOUNT ON
IF UPDATE( Dollars)
BEGIN
UPDATE T SET
Dollars = dbo.DollarsUpdate( New.ID, T.TextValue, Other.Fiscal_Year)
FROM
TableName T
join
Inserted new on T.id = new.id
join
Deleted old on new.id = old.ID
join
AnotherTableName Other on Other.ID = new.id and Other.OtherID = T.OtherID --???
WHERE Old.Dollars <> New.Dollars -- If this column can be null you needto add two more checks
END
[Edited] : I just copied and pasted from the wrong Place
* Noel -
The "inserted" table is available on INSERT,UPDATE triggers
the Deleted table is available on UPDATE,DELETED triggers.
You need to make sure your triggers will handle dataSets. This trigger does not, it is structured to handle only single inserts. If you inserted multiple rows in a single insert (Insert, Select) your data will not be right. you say "So, I only insert 1 record at a time" this may be true, but some day you may not, and this trigger will screw U.
For the insert try an instead of for the insert.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tr_i_Dollars]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[tr_I_Dollars]
GO
CREATE TRIGGER tr_I_Dollars
ON dbo.TableName
INSTEAD OF INSERT
AS
INSERT INTO dbo.TableName (ID, TextValue, Dollars, ..., ... All other fields here)
Select ID, TextValue, dbo.DollarsUpdate( ID, TextValue, Fiscal_Year), ..., ...)
From INSERTED A
INNER JOIN AnotherTable m on m.OtherID = a.OtherID
GO
For updates does the dollars field actually get updated, or just the textual value?
Try this for update trigger.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tr_U_Dollars]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[tr_U_Dollars]
GO
CREATE TRIGGER tr_U_Dollars
ON dbo.TableName
AFTER UPDATE
AS
if UPDATE(TextValue)
Begin
UPDATE A
set Dollars = dbo.DollarsUpdate( ID, TextValue, Fiscal_Year)
From TableName a
INNER JOIN Inserted b on a.id = b.id
INNER JOIN AnotherTable m on m.OtherID = a.OtherID
end
GO
-
Noeld,
Yet again, almost the same answer,
Yet you beat me to the punch.
had to rewrite mine, damn time outs.
-
You trigger is coded in a complicated and procedural manner where a single statement that does the update whenever the dependent values change would be simplier.
Try this:
CREATE TRIGGER Dollars ON dbo.TableName AFTER UPDATE
AS
IF @@rowcount = 0 return
set nocount on
set xact_abort on
UPDATE TableName
SET Dollars = dbo.DollarsUpdate( inserted.Id, inserted.TextValue, AnotherTable.Fiscal_Year)
from AnotherTable
JOIN inserted
on AnotherTable.OtherID = inserted.OtherID
JOIN deleted
in deleted.id = inserted.id
wheredeleted.TextValue != inserted.TextValue
ORdeleted.OtherID != inserted.OtherID
-- handle change between null and not null
or (deleted.TextValue is null
and inserted.TextValue is not null)
or (deleted.TextValue is not null
and inserted.TextValue is null)
SQL = Scarcely Qualifies as a Language
-
Yep Got Hit by timeouts more than once today too
* Noel -
the IF UPDATE() will speed up things in case where no action is needed
* Noel -
Thank you all! Unfortunately, it is not working when I do an Update, (have not tried the Insert yet). Please note: The actual column Updated is not Dollars, it is another $ column I have entitled Amount. I have tried to write this so none of our actual fields and table names are used, if I missed something and a column has its old name, please forgive me... (Also, I am not sure what is going with the TextValue IS/NOT NULL check...).
This is what I have re-written:
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsUpdate]')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsUpdate]
GO
CREATE TRIGGER DollarsUpdate
ON dbo.TableName
AFTER UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
IF UPDATE( Amount)
BEGIN
BEGIN TRANSACTION UpdateDollars
UPDATE TableName SET
Dollars = dbo.RMS_Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year)
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN deleted Old ON( TableName.[ID] = Old.[ID])
INNER JOIN OtherTable ON( TableName.OtherID = OtherTable.OtherID)
WHERE Old.Dollars <> New.Dollars
AND( Old.TextValue IS NULL AND New.TextValue IS NOT NULL) -- handle change between null and not null
OR( Old.TextValue IS NOT NULL AND New.TextValue IS NULL)
COMMIT TRANSACTION UpdateDollars
END
------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsInsert]')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsInsert]
GO
CREATE TRIGGER DollarsInsert
ON dbo.TableName
AFTER INSERT
AS
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
BEGIN
BEGIN TRANSACTION InsertDollars
UPDATE TableName SET
Dollars = dbo.RMS_Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year)
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN deleted Old ON( TableName.[ID] = Old.[ID])
INNER JOIN OtherTable ON( TableName.OtherID = OtherTable.OtherID)
WHERE Old.Dollars <> New.Dollars
AND( Old.TextValue IS NULL AND New.TextValue IS NOT NULL) -- handle change between null and not null
OR( Old.TextValue IS NOT NULL AND New.TextValue IS NULL)
COMMIT TRANSACTION InsertDollars
END
I wasn't born stupid - I had to study.
-
Unfortunatelly debugging triggers sucks.
First of all you do not need transactions in the trigger.
Transactions are implicit in triggers if trigger fails, the transaction will rollback including the original insert/update
I removed the stuff you had in the where clause, and your join to the deleted table, It adds confusion, and It could be part of why its not updating. Other than having issues in your function it should work. but of course I could be, and am often wrong.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsUpdate]')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsUpdate]
GO
CREATE TRIGGER DollarsUpdate
ON dbo.TableName
AFTER UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
IF UPDATE( Amount)
BEGIN
UPDATE TableName SET
Dollars = dbo.RMS_Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year)
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN OtherTable ON( TableName.OtherID = OtherTable.OtherID)
-- For debugging purposes put a select statement to see if the results are evaluating correctly
-- Remove this if if it appears to be returning correctly
Select Dollars, dbo.RMS_Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year) as new dollars
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN OtherTable ON( TableName.OtherID = OtherTable.OtherID)
END
-
Farrell,
Besides What Ray Already mentioned about the wrapping Transacrtions command being unnecessary your search filter must be:
WHERE Old.Dollars <> New.Dollars
OR( Old.TextValue IS NULL AND New.TextValue IS NOT NULL) -- handle change between null and not null
OR( Old.TextValue IS NOT NULL AND New.TextValue IS NULL)
Cheers
* Noel -
Alright. I've changed these as suggested. Note: I also removed the IF @@ROWCOUNT = 0 RETURN. And I changed the Update/Insert constraint to use [Amount] instead of Dollars ( WHERE Old.[Amount] <> New.[Amount] )
The Insert Trigger does not work. The Update Trigger uses the old Amount value to calculate the new Dollar value. (It is closer than before, but it obviously is not working AFTER the Update...).
TIA. (And thanks for keeping with me on this one...)
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsUpdate]')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsUpdate]
GO
CREATE TRIGGER DollarsUpdate
ON dbo.TableName
AFTER UPDATE
AS
--IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
IF UPDATE( [Amount])
BEGIN
UPDATE TableName SET
Dollars = dbo.Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year)
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN deleted Old ON( TableName.[ID] = Old.[ID])
INNER JOIN OtherTable ON( TableName.appl_id = OtherTable.appl_id)
WHERE Old.[Amount] <> New.[Amount]
END
------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DollarsInsert]')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[DollarsInsert]
GO
CREATE TRIGGER DollarsInsert
ON dbo.TableName
AFTER INSERT
AS
--IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON
BEGIN
UPDATE TableName SET
Dollars = dbo.Dollars( New.[ID], TableName.TextValue, OtherTable.Fiscal_Year)
FROM TableName
INNER JOIN inserted New ON( TableName.[ID] = New.[ID])
INNER JOIN OtherTable ON( TableName.appl_id = OtherTable.appl_id)
WHERE TableName.[Amount] <> New.[Amount]
END
------------------------------------RESULTS----------------------------------------------
SELECT a.[ID], a.[Amount], a.Dollars, a.TextValue, m.Fiscal_Year
FROM TableName a
INNER JOIN OtherTable m ON( a.OtherID = m.OtherID)
WHERE a.[id] = 714199
/* [ID] [Amount] Dollars TextValue Fiscal_Year
714199 252,960.00 0 Value 2004
749404 259,352.00 0 Value 2005 */
UPDATE TableName SET
Dollars = 100
WHERE [ID] = 714199
SELECT a.[ID], a.[Amount], a.Dollars, a.TextValue, m.Fiscal_Year
FROM TableName a
INNER JOIN OtherTable m ON( a.OtherID = m.OtherID)
WHERE a.[id] = 714199
/* [ID] [Amount] Dollars TextValue Fiscal_Year
714199 100.00 11,624.00 Value 2004
749404 259,352.00 0 Value 2005 */
I wasn't born stupid - I had to study.
-
On the update trigger you are using IF UPDATE([Amount]):
But this statement
UPDATE TableName SET
Dollars = 100
WHERE [ID] = 714199
Updates Dollar not Amount therefore nothing happens
On the insert Trigger just remove the WHERE Clause!!
* Noel -
The Update works perfectly. (I originally wrote the function using the Original table name we will inevitbably use this function upon, then I made a test table with another name. When I changed that in the function, the Update trigger works perfectly. The function uses [Amount] and a percentage from another table to calculate the Dollars value).
I will drop the WHERE clause in the Insert Trigger and see what that gets me.
Thanks!!
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply