asked on

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

ASKER CERTIFIED SOLUTION

Jarrod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gudidi

ASKER

can't i make just output from deleted?

waltersnowslinarnold

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
}
}

gudidi

ASKER

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.

Jarrod

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 :)

waltersnowslinarnold

Yes you can, but check your Trigger is in After, neither For nor Instead Of trigger.

Jarrod

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

gudidi

ASKER

10x

the output is works for temp table.

10x a lot

Jarrod

#delete will act the same as deleted in the trigger senario

apexpert

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