SQLTeam.com | Weblogs | Forums

How to view the contents of a trigger


#1

Hi,

I have been looking around to see how I can view the contents of the Inserted and Deleted virtual tables of a trigger. And the few places that I have seen on the net does telly ou how to fully do it.
For example, one place said to do something like this:

INSERT INTO Some table
Select column
From Inserted

However, I have not found how to incorporate that into my trigger to get the data.I have a big trigger and I want to get all of the inserted fields into a table to view them.
Below is a small sample of the trigger that I am using. so here I would want to see in the table the inserted values for account_type and accrued_interest.

Can someone tell me how to do that?

Thank you

IF UPDATE([account_type])
INSERT INTO Changes
(file_no, table_name, changed_from,
changed_to,
changed_column, created, createdby)
SELECT i.file_no, 'DebtorInfo', ISNULL(CAST(d.[account_type] as varchar ), ''),
ISNULL(CAST(i.[account_type] as varchar ), ''),
'[account_type]', CURRENT_TIMESTAMP, suser_sname()
FROM Inserted i
LEFT OUTER JOIN Deleted d on i.file_no = d.file_no
WHERE ( d.[account_type] <> i.[account_type]
OR (d.[account_type] IS NULL AND i.[account_type] IS NOT NULL)
OR (d.[account_type] IS NOT NULL AND i.[account_type] IS NULL)
)

IF UPDATE([accrued_interest])
INSERT INTO Changes
(file_no, table_name, changed_from,
changed_to,
changed_column, created, createdby)
SELECT i.file_no, 'DebtorInfo', ISNULL(CAST(d.[accrued_interest] as varchar ), ''),
ISNULL(CAST(i.[accrued_interest] as varchar ), ''),
'[accrued_interest]', CURRENT_TIMESTAMP, suser_sname()
FROM Inserted i
LEFT OUTER JOIN Deleted d on i.file_no = d.file_no
WHERE ( d.[accrued_interest] <> i.[accrued_interest]
OR (d.[accrued_interest] IS NULL AND i.[accrued_interest] IS NOT NULL)
OR (d.[accrued_interest] IS NOT NULL AND i.[accrued_interest] IS NULL)
)


#2

you don't need the left outer join on Deleted, since it's an update. I assume that File_No is the PK . How wide is the table? It could be easier to just dump all Inserted and deleted columns to another table to view. One caveat though, triggers can cause blocking/locking issues. If this is a large trigger as you say and the table is highly volatile, it could lead to performance issues. Can you provide ddl and sample data as well as what you are trying to accomplish? The main question I would have is why are you checking each column? If that is the case, it would be simpler (and better performant) to implement Change tracking


#3

Hi
I am sorry I think I did not state what I am looking for right. Sorry.
The trigger works, I just been given that for context.

Right now the changes do go to a change table. I just would like a way to be able to send the Inserted and the Deleted values to another table separately.

to do that I need to be able to do something like this but where do ai put this for each column in my trigger
INSERT INTO Some table
Select column
From Inserted