How to Check all Records in a TRIGGER

[faccorp]

I have a trigger that appears to work fine, however I want to make sure that it can handle multiple records in the inserted or deleted tables. Plus I few other questions.

The current trigger is something like

CREATE TRIGGER [SetData] ON dbo.Transactions 

/*

The basic purpose of this trigger is to

1.  Set the 'REF' column equal to the IDENTITY column when an new Transaction is Inserted and the Transaction type is a '0'

2.  Automatically set a 'Cleared' bit in the invoice record if the sum of all payments is equal to the sum of the invoice.

*/

FOR INSERT, UPDATE, DELETE
AS

DECLARE @SumInv MONEY
DECLARE @SumPay MONEY
DECLARE @Ref INT
DECLARE @InvID INT
DECLARE @Type INT
DECLARE @Id INT


SET @Type =  (Select [Type] from inserted)
SET @Id = (SELECT [ID] from inserted)
SET @Ref = (SELECT [REF] FROM inserted UNION SELECT [REF] FROM deleted)

/* Transactions that are Type 0 are invoices. For new invoices, I want to set the 'REF' column equal to the Identity column. */

IF EXISTS (SELECT * FROM inserted)
BEGIN
	IF (@Type = 0)
	BEGIN
		UPDATE Transactions
		SET REF = @Id
		WHERE [ID] = @Id
		SET @Ref = @Id		
	END
END



/* Find the Invoice ID.  It's ID is equal to the REF and must be an Invoice (type = 0) */

SELECT @InvID = (SELECT dbo.Transactions.[ID]
FROM dbo.Transactions
WHERE (dbo.Transactions.[ID] = @Ref AND dbo.Transactions.[Type] = 0))


/* If the InvID is null, then it was deleted - so ignore everything. */

IF (@InvID IS NULL)
BEGIN
	RETURN
END


SELECT @SumInv = (SELECT Amount
FROM dbo.Transactions
WHERE (dbo.Transactions.[ID] = @Ref))

SELECT @SumPay = (SELECT SUM(Amount)
FROM dbo.Transactions
WHERE (dbo.Transactions.REF = @Ref AND dbo.Transactions.[Type] > 0))
	

IF (@SumPay + @SumInv = 0)
BEGIN
	UPDATE Transactions
	SET [Invoice is Cleared] = 1
	WHERE dbo.Transactions.[ID] = @InvID	
END
ELSE
BEGIN
	UPDATE Transactions
	SET [Invoice is Cleared] = 0
	WHERE dbo.Transactions.[ID] = @InvID
END

I have 3 questions.

1. Since my trigger is modifiying the table that initiates the Trigger, am I then causing another trigger to take place.

2. Is my method of recalling the IDENTITY value for inserted records legitimate? My identity key is Transactions.[ID]? Should I be using SCOPE_IDENTITY() ?

3. How do I make this trigger traverse throughout multiple records of the inserted and deleted tables.


Thank you.

 

[SQLSister]

No this will not work for multiple records.

use a join to the inserted table in your update statements instead. See BOL UPdate statment description for how to refernce another table in an Update.

You should not need scope_identity here as the inserted table holds the ID value.

in answer to question 1, yes you may be invoking another trigger, depends on whether the table you are updating has one.

Questions about posting. See faq183-874

 

[faccorp]

I'm not quite sure how to implement the Join command on the inserted/deleted table. Since the trigger performs multiple function I'm getting a bit confused as to how to code the Join. Could you provide an example?

Regarding 'invoking another trigger', I am updating the same table that initiated the trigger. Does this initiate another trigger then?

Thanks SQLSister

 

[SQLSister]

UPDATE Transactions
SET REF = inserted.ID
from Transactions inner join inserted
ON Transactions.ID = inserted.ID

Questions about posting. See faq183-874

 

[faccorp]

Thanks, but the problem I have is I am setting variables that are used elsewhere in the trigger. The variables are based off of the inserted and/or deleted tables. So the whole trigger needs to be able to traverse through the inserted or the deleted tables.

Also, I get an error when I use 'inserted.ID'. It says that ID is an invalid column.

And then assuming I can work this out an make it work, how do I also handle the deleted table?


Thanks,

 

[ESquared]

If you are doing some process with variables which must be done once for each inserted row, then you must use a loop.

You can use a cursor, or you could use a method I have used which goes something like:

-- Handle multiple rows inserted in one statement, without a cursor
-- If a large volume of rows are inserted at once, this might need rewriting as the Count method below could become expensively inefficient

DECLARE
	@RowCount int,
	@Variable1 int,
	@Variable2 int,
	@Variable3 int,
	@Variable4 int

SELECT @RowCount = Count(1) FROM Inserted
WHILE @RowCount > 0 BEGIN
	SELECT
			@Variable1 = Column1,
			@Variable2 = Column2,
			@Variable3 = Column3,
-- those three columns comprise a composite key which has an implied precedence of, 1, 2, 3.
-- this trigger runs on a table which is a history of a process, so it must be done in order.
			@Variable4 = Column4,
		FROM Inserted I
		WHERE @RowCount =
			(
				SELECT Count(*)
				FROM Inserted I2
				WHERE
					I2.Value1 > I.Value1
					OR (
						I2.Value1 = I.Value1
						AND (
							I2.Value2 > I.Value2
							OR (I2.Value2 = I2.Value2 AND I2.Value3 >= I.Value3)
						)
					)
			)
	EXEC StoredProcedure
			@Variable1 = @Variable1,
			@Variable2 = @Variable2,
			@Variable3 = @Variable3,
			@Variable4 = @Variable4,
   -- or whatever process, a SP is probably better
	SET @RowCount = @RowCount - 1
END

I took this from a trigger that is in use in production. In reality, multiple rows really aren't inserted at a time, but I wanted to make sure. In this case, it's possible the cursor would be more efficient!

I can't think of a way to do it without a cursor that would definitely be more efficient.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[SQLSister]

Looks to me like all those variables you reference come out of the inserted ro deleted tables so all those statements can be rewritten as updates with joins as well.

Try inserted.[ID]

ID is not a good choice for the name of the ID field. It is much better to specify the ID, CustomerID or ItemID, etc. When you call the id field in every table ID, then writing the code for joins and selecting values can get confusing. If you call it something descriptive fromthe start, you always know what the information inthe id field is and can easily tell how it relates to other tables as you can use the same name in both tablles.

Questions about posting. See faq183-874