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)
)