?
Solved

trigger syntax MSSQL

Posted on 2010-01-05
10
Medium Priority
?
359 Views
Last Modified: 2012-05-08
Hi Experts

i am trying to test a trigger that could have a problem so i try to run it in analyzer
but the i get error that deleted table is invalid object.
this is the code part:

begin transaction
delete from ESL_SETTING_DATA where ID_1=4211
SELECT esl_setting_data_id FROM DELETED
commit transaction

i will appreciate your help
10x
0
Comment
Question by:gudidi
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Accepted Solution

by:
zadeveloper earned 2000 total points
ID: 26179094
This is beacause in the trigger environment - after deleted, there is a "new table" created on the fly called deleted with the original values in it.
To Mimic this - create a table (same structure as original delete row) - call it deleted and populate it with a record - which you will be pretending to delete.
 
(be sure not to copy that into the trigger as the object exists there already) - you will need to use [deleted] as deleted is a reserved word :)
0
 

Author Comment

by:gudidi
ID: 26179104
can't i make just output from deleted?
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179112
Try the following code snippet..,

CREATE TRIGGER trigger_name
ON tablename
{
    FOR  DELETE      
        AS      
        begin transaction
        delete from ESL_SETTING_DATA where ID_1=4211
        SELECT esl_setting_data_id FROM DELETED
        commit transaction
    }
}
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gudidi
ID: 26179129
hi guys

i was not clear, i have the trigger and i have to test it if the correct value in it.
i want to know if there is a way to output the deleted into anlyzer query.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26179136
can't i make just output from deleted? : nope as the deleted object only exists in a trigger for delete environment. so if you want to debug the sql you will need to create an object that "pretend" to be the deleted row-table object :)
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179158
Yes you can, but check your Trigger is in After, neither For nor Instead Of trigger.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26179167
try this for debugging / running in analyser

select * into #delete from ESL_SETTING_DATA  where ID_1=4211

delete from ESL_SETTING_DATA where ID_1=4211
SELECT esl_setting_data_id FROM #delete

if exists (select * from tempdb..sysobjects where name like ('%#delete%'))
drop table #delete

Open in new window

0
 

Author Comment

by:gudidi
ID: 26179168
10x

the output is works for temp table.

10x a lot
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26179172
#delete will act the same as deleted in the trigger senario
0
 
LVL 4

Expert Comment

by:apexpert
ID: 26179175
CREATE TRIGGER trigger_name
ON tablename
{
    FOR  DELETE      
        AS      
        begin transaction
        delete from ESL_SETTING_DATA where ID_1 in (SELECT esl_setting_data_id FROM DELETED)
        SELECT esl_setting_data_id FROM DELETED
        commit transaction
    }
}

try it...hope so it works
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question