469,647 Members | 1,787 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

how to make an Update trigger capture the foreign key text value

I have an update trigger on a table called 'tbl_br' that is used to audit column changes and populate those changes into an audit table called 'tbl_audit'. See an excerpt of the code below:

Expand|Select|Wrap|Line Numbers
  1. Declare @LoginId as nvarchar (8)
  2. set @LoginId = dbo.fnGetLoginId()
  3.  
  4. Declare @status_id_new int
  5.  
  6. Declare @id_old as int
  7. Declare @status_id_old int
  8.  
  9. Select  @id_old  = (Select id from Deleted)
  10. Select  @status_id_new  = (Select status_id from tbl_br where id = @id_old)
  11.  
  12. Select  @status_id_old  = (Select status_id from Deleted)
  13.  
  14. if isnull(@status_id_new,0) <> isnull(@status_id_old,0) 
  15. begin
  16. Insert into dbo.tbl_Audit(changed_on, changed_by, table_name, table_Record_id,  Field_Name, Old_Value, new_Value)
  17. values(Getdate(),@LoginId,'tbl_br', @id_old, 'status_id',  @status_id_old,  @status_id_new)
  18. end
The problem with this code is that it inserts the numeric value of 'status_id' instead of the actual descriptive status text value from the table 'tbl_status'. Since audit reports will be run regularly by end-users for various reasons, we would like to show the text value of the foreign key instead of the id. I know there needs to be a join to the foreign key table (ON tbl_status.id = tbl_br.status_id) to get the text value, but I'm not certain how exactly this should be written.

Any insight is greatly appreciated!
Jun 19 '07 #1
0 968

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Galina | last post: by
2 posts views Thread by Gunnar Vřyenli | last post: by
7 posts views Thread by Justin | last post: by
25 posts views Thread by Neo Geshel | last post: by
4 posts views Thread by Joerg Gempe | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.