January 1, 2008 at 6:20 am
please tell me how can i get the records from inserted/deleted virtual tables in sql.....
January 1, 2008 at 8:19 am
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
January 2, 2008 at 4:54 am
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
January 2, 2008 at 6:03 pm
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?
January 3, 2008 at 3:47 am
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
January 3, 2008 at 4:00 am
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
January 3, 2008 at 4:33 am
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
January 3, 2008 at 5:36 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply