Access inserted, deleted virtual table in the SP
-
I want to create Insert, Update, Delete trigger and put the logics in stored procedure and have the trigger execute the stored procedure accordingly.
Can I access the inserted, deleted virtual table in the stored procedure?
-
no, context will have changed. The logic needs to be in the trigger.
-
As a side note, be careful when calling stored procedures through your triggers. Anything you add to your triggers is going to prolong your transactions and could lead to more blocking/waiting situations in SQL Server.
-
Thanks for your reply. Looks like logic has to be in trigger. : (
I understand that after trigger in SQL 2000 fire on per stmt basis. Thus, I need to write trigger to cater for mutiple rows that will be inserted, updated, and deleted. Is cursor a good way to go about do it?
-
REALLY bad.
_____________
Code for TallyGenerator -
old hand,
What do you suggest? Will WHILE loop better than cursor?
-
When you say multiple rows, I assume you mean that you want to perform some process based on all or a subset of rows that exist in the inserted/deleted virtual tables? This can be done. The inserted/deleted tables can contain multiple values depending on the number of rows affected by the insert/delete statement that caused the trigger to fire. For example, say you run an UPDATE command and it performs updates to 10 rows in your <table>. Through logic in your update trigger for your <table>, you should find 10 rows in both the inserted and deleted tables (because SQL Server treats an update as a insert and delete) for each of the rows that your update statement touched. Here's a trigger code example that will update rows in another table based off of a certain column being updated by an update statement, kind of like writing your own cascading update:
IF UPDATE(<column name>
BEGIN
UPDATE <table2>
SET <column name> = Inserted.<inserted column name>
FROM <table2>, Inserted
WHERE <table2>.<column name> = Inserted.<inserted column name>
END
-
The logic is more complex than just direct update to another table from inserted and deleted virtual table.
I need to process row-by-row in inserted and deleted. That's why my concern is would while loop perform better than cursor? I have been using cursor for DBA maintenance scripts and it give me no problem. Can someone show me some code of while loop that can replace cursor? Appreciate some kind soul out there.
-
What kind of logic could possibly require to use a cursor in trigger?
_____________
Code for TallyGenerator -
If you need to process, row by row, I would suggest selecting from inserted/deleted and working off of temporary tables. This would allow you more flexability with how you handle your data. For example:
Create table #temp_Processing (
RecordID identity(1,1),
ColumnA int,
ColumnB int )
<populate temp table from inserted/deleted here>
Select @key = min(RecordID) from #temp_Processing
While @key is not null
Begin
<Process data here>
Select @key = Min(RecordID) from #temp_processing where RecordID > @key --increments key value
end
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply