Help on triggers

Question

Post reply

Help on triggers

vignesh.ms

Hall of Fame

Points: 3456
More actions
June 30, 2013 at 9:52 am

#275706

Dear Friend,
Am I able to capture the query inside the trigger that I was executed..?
For example, I have instead of delete trigger for a table say SampleTable.
create table sampletable (Id int ,name varchar(800))
create trigger sampletriger on sampletable
instead of delete
as
Print 'Desired Query'
insert into sampletable values(1, 'heavenguy')
insert into sampletable values(2, 'tristan')
insert into sampletable values(3, 'Jack')
Now i'm trying to delete the sampletable...
delete from sampletable where id = 1
I wanted the trigger to return the query that I executed...
output:-
delete from sampletable where id = 1

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

you could experiment with your trigger using :

SELECT T.text

FROM sys.dm_exec_requests R

cross apply sys.dm_exec_sql_text( R.sql_handle ) T

WHERE R.session_id = @@spid

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

Hi I done like this

create trigger heaven on tablename instead of delete as

SELECT T.text FROM sys.dm_exec_requests R cross apply sys.dm_exec_sql_text( R.sql_handle ) T WHERE R.session_id = @@spid

but im getting the same result of create trigger statement

pls help

i want to show the delete query which causes the trgger

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

vignesh.ms (8/14/2013)
i want to show the delete query which causes the trgger

It might be helpful to understand why you want to do this.

For example, are you trying to prevent certain deletes from happening?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

yes of course I want to restrict delete query based on some condition.

for example ,

assume a table named sample, and one of the column is tag which holds value 1,2

trigger should allow users to delete the rows which has tag = 1 not to others.

Can't Run:

delete from sample where tag = 1

Can Run:

delete from sample where tag = 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

vignesh.ms (8/15/2013)
yes of course I want to restrict delete query based on some condition.
for example ,
assume a table named sample, and one of the column is tag which holds value 1,2
trigger should allow users to delete the rows which has tag = 1 not to others.
Can't Run:
delete from sample where tag = 1
Can Run:
delete from sample where tag = 1

Wouldn't it be easier to look for the condition

tag = 1

rather than

query = 'delete from sample where tag = 1'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 6

this sounds good ..

kindly share me the query to create a trigger to achive this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

CREATE TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger

ON dbo.My_Table

INSTEAD OF DELETE

AS

BEGIN

IF NOT EXISTS (SELECT 1 FROM deleted WHERE tag <> 1)

DELETE FROM MyTable

WHERE <key> IN (SELECT <key> FROM deleted)

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

OTF SSCertifiable Points: 6104 More actions · Answer 8

Rather than hide that logic in a Trigger, wouldn't it be better to just deny your users direct access to the base table and then use a Stored Procedure to control access?

You can encapsulate all the logic you want to control the Deletes in there.