magic table in sql

  • please tell me how can i get the records from inserted/deleted virtual tables in sql.....

  • The inserted and deleted tables are only available inside triggers. In a trigger, you can select from them just like any other table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is a link that will explain the use of both the inserted and deleted tables

    http://msdn2.microsoft.com/en-us/library/ms191300.aspx

    If you need more help let me know

    Kevin Brennan M.Sc MCDBA, MCSE, MCP+I

    CodeMinkey

  • Be careful to read about the inserted and deleted tables in the appropriate versions of BOL. those tables are more available in new places in 2005 they were not in 2000 (for example in the OUTPUT clause of any INSERT/UPDATE/DELETE statement).

    The 2000 version of the article Kevin was referring to is:

    http://msdn2.microsoft.com/en-us/library/aa214435(SQL.80).aspx

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Create Table Emp

    (

    Eno int,

    age Int

    )

    Create Clustered index id1 on Emp(Eno)

    or you can use Alter Table statement to create clustered index.

    Anyway come to the point.

    Now you have a insert trigger on Emp table.

    Create Trigger Emp_Insert

    on Emp

    for Insert

    begin

    declare @Age int

    select @Age = inserted.Age from inserted -- You can't use this table outside of trigger.

    If @Age > 99

    Begin

    Print 'Invalid Age...Try Again"

    Rollback

    End

    End - End of Trigger

    In similar way ,you can use the deleted table also. But keep in mind you can't use magic table outside of the trigger.

    Insert into emp values(1,50) - Should Insert

    Insert into emp values(1,100) - Won't insert.Explain Why?

    karthik

  • karthikeyan (1/3/2008)


    select @Age = inserted.Age from inserted -- You can't use this table outside of trigger.

    And what happens if I insert more than 1 row in a single statement?

    -- How many rows will be inserted?

    insert into Emp

    SELECT 1 as EmpNo, 50 AS Age

    union all

    SELECT 2 AS EmpNo, 100 AS Age

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes,Local variables can store single value at a time.But just for example i explained to him how inserted table get affected within a trigger.

    karthik

  • For completeness, and so no one gets the wrong idea about how to write triggers.

    A trigger fires once for a statement. The inserted and/or deleted tables will contain as many rows as the statement affected. If an insert inserts 6 rows then the inserted table will contain 6 rows.

    All triggers should be written in such a way that they can handle any number of rows in the inserted/deleted tables.

    Triggers should not do things like store values from inserted/deleted in variables as that could give unexpected and inconsistant results when the trigger fires for more than one row.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply