November 27, 2012 at 10:36 am
I have a trigger that is supposed to fire when I enter a transaction from my webpage... this transaction then gets a number automatically... I also need it to reference the original related entry which appears on the screen as "Original Number", but the trigger only references the current inserted number... any ideas?:
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trTagTransfered]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
IF (SELECT Transaction_Type FROM INSERTED) = 'Transfer'
UPDATE tblTransactions
SET [Transfered] = 'True'
WHERE tblTransactions.OriginalTransactionNumber in (SELECT OriginalTransactionNumber FROM INSERTED)
END
November 27, 2012 at 10:51 am
...
CREATE TRIGGER [dbo].[trTagTransfered]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
IF (SELECT Transaction_Type FROM INSERTED) = 'Transfer'
UPDATE tblTransactions
SET [Transfered] = 'True'
WHERE tblTransactions.OriginalTransactionNumber in (SELECT OriginalTransactionNumber FROM INSERTED)
END
Your trigger will fire on any insert to tblTransactions.
Will it do anything? That will depend on what is inside "INSERTED".
The way trigger is written, makes it not very reliable, as if you will insert multiple records into tblTransactions in one go, then your "IF (SELECT Transaction_Type FROM INSERTED) = 'Transfer'" check may or may not work. Let say you insert two records and only one of them has Transaction_Type = 'Transfer'. There is no guarantee that "SELECT Transaction_Type FROM INSERTED" will return Transaction_Type of this particular record!
Now, if your "IF" condition pass, then all rows of tblTransactions will be updated where their OriginalTransactionNumber is in a list of OriginalTransactionNumber from INSERTED.
Also, I cannot really understand what do you mean by "I also need it to reference the original related entry which appears on the screen as "Original Number", but the trigger only references the current inserted number". Is the "Original Number" displayed on your screen is the same as tblTransactions.OriginalTransactionNumber?
November 27, 2012 at 11:00 am
Check the sql server trigger options... Recursive triggers and triggers within triggers options, that seems to be your case, have to be enabled... be careful with these options..
November 27, 2012 at 11:05 am
The trigger is reliably setting [Transfered] = 'True' if the condition of the INSERTED Transaction type is 'Transfer', but I want it to update not the INSERTED record but a record referenced on my form/webpage... what this is is I'm creating a new transaction for an inventory item, but I want to make unavailable the corresponding earlier product in the earlier transaction for which I have that number at my disposal to use in my trigger.
November 27, 2012 at 11:10 am
i think your trigger needs to be like this;
affect only the inserted records that actually also have [Transfered] = 'True';
your current trigger would affect all records being inserted at this time if even one record had the matching criteria.
ALTER TRIGGER [dbo].[trTagTransfered]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
UPDATE tblTransactions
SET [Transfered] = 'True'
FROM INSERTED
WHERE tblTransactions.OriginalTransactionNumber = INSERTED.OriginalTransactionNumber
AND INSERTED.Transaction_Type = 'Transfer'
END --TRIGGER
Lowell
November 27, 2012 at 11:19 am
briancampbellmcad (11/27/2012)
The trigger is reliably setting [Transfered] = 'True' if the condition of the INSERTED Transaction type is 'Transfer'...
It does, but only if you insert one record at the time. However, it is not right way to write a trigger, so it can work properly for one affected row only... Your trigger should work properly regardless of how many affected rows will be in INSERTED (or DELETED)
In your case, it can be done like that:
CREATE TRIGGER [dbo].[trTagTransfered]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
UPDATE tblTransactions
SET [Transfered] = 'True'
WHERE tblTransactions.OriginalTransactionNumber in (SELECT OriginalTransactionNumber FROM INSERTED WHERE Transaction_Type = 'Transfer')
END
I hope you can spot the difference.
You should remember that if you modify multiple records by single query, the relevant trigger will fire only once but internal INSERTED and DELETED tables will contain all affected rows.
...but I want it to update not the INSERTED record but a record referenced on my form/webpage... what this is is I'm creating a new transaction for an inventory item, but I want to make unavailable the corresponding earlier product in the earlier transaction for which I have that number at my disposal to use in my trigger.
How you can identify the "record referenced on my form/webpage" in the trigger?
What is "corresponding earlier product"?
What do you mean by " make unavailable " ... "in the earlier transaction for which I have that number at my disposal to use in my trigger"?
Sorry man, but I have lost my crystal ball, so I cannot longer see what you can see on your screen and I cannot really see your database structure too...
If you don't mind reading the article from the link at the bottom of my signature, you may be able to provide a bit more relevant details so we could help you a bit better.
November 27, 2012 at 11:23 am
...
your current trigger would affect all records being inserted at this time if even one record had the matching criteria.
...
or not affect any records being inserted at this time at all, even if one record had the matching criteria, as it could be not the one picked up by SELECT in IF...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply