28.3.08

Magic Tables in SQL Server

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword "deleted"

To project the deleted data into the deleted table we will use "output" clause
Below is the sample code to retrieve the deleted data.
Code Snippet

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20))

DELETE VENKATOutput
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable

Similarly, we can retrieve the updated data and old data too using the keyword "Inserted"

Provide me your valuable feedback regarding this.
Regards,
Venkatesan Prabu . J

at

6 comments:

  1. SundarJuly 11, 2008 at 9:35 PM

    Hi Venkat,
    Im Sundar. First, I would like to say thanks. Then sorry, i cant understand ur code. Can you explain clear?
    And one more things is i have more than 150 tables in my office. so i cant write this for 150 tables. so I need one code for all my 150 tables.
    Thanks a lot.

    ReplyDelete
  2. SundarJuly 11, 2008 at 10:17 PM

    Hi Venkat,
    Im Sundar. First, I would like to say thanks. Then im sorry, I cant understand ur code. Can u explain clear?
    And one more thing is i have more than 150 tables in my office. I cant write this code for 150 tables. So i need one code it will affact all my 150 tables. Can u help me?

    ReplyDelete
  3. Venkatesan PrabuJuly 19, 2008 at 11:22 PM

    Thanks for writing your feedback in my blog.

    Ok, Let me explain you about the article.

    Magic tables are inherent property of a trigger.

    Suppose considering am writing a trigger to update a row.
    Suddenly, I thought like, I need my old value. Is it possible to retrieve it?

    Yup, Its possible by using Magic tables. The old value get stored in the magic table.
    If we run the second trigger the second trigger data will get stored into the magic table.

    Any suggestions, feedback... or if you want more info.. please let me know.
    Regards,
    Venkatesan prabu .J

    ReplyDelete
  4. kalyanJune 24, 2009 at 4:32 AM

    Thanks Venkat

    asking about to explain the query what you have written. Not, what is Magic table!

    what is that VENKAToutput and so many variables you used there.

    please explain in detail line by line

    Thanks
    Kalyan

    ReplyDelete
  5. AnonymousSeptember 20, 2010 at 10:59 PM

    Thanks Venkat,
    God Bless U
    Take Care n
    Keep Smiling

    Warm Regards
    Sagar Vernekar

    Software Engineer

    Mob - +91-9881640866

    ReplyDelete
  6. Rahul ChoudharyFebruary 17, 2011 at 3:04 AM

    hi Venkatesan prabu,

    really this is very nice.because we have all information data which have been update or delete from table.

    ReplyDelete