trigger syntax MSSQL

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
gudidiAsked:
Who is Participating?
 
zadeveloperConnect With a Mentor Commented:
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
 
gudidiAuthor Commented:
can't i make just output from deleted?
0
 
waltersnowslinarnoldCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
gudidiAuthor Commented:
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
 
zadeveloperCommented:
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
 
waltersnowslinarnoldCommented:
Yes you can, but check your Trigger is in After, neither For nor Instead Of trigger.
0
 
zadeveloperCommented:
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
 
gudidiAuthor Commented:
10x

the output is works for temp table.

10x a lot
0
 
zadeveloperCommented:
#delete will act the same as deleted in the trigger senario
0
 
apexpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.