Help with tsql querry

Question

Help with tsql querry

raza.qadri

Mr or Mrs. 500

Points: 554
More actions
March 19, 2015 at 7:47 am

#295266

can someone help me with the following code:
i get error "There is already an object named '#trig' in the database."
Declare @job_order varchar(10)
IF EXISTS (SELECT * FROM DELETED)
begin
select * into #trig from deleted
end
else
begin
Select * into #trig from inserted
end
Set @job_order = Rtrim(Ltrim((SELECT job_order FROM #trig)))
drop table #trig

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're creating the temp table twice within the batch. The parser doesn't execute conditionals, it just sees two creations of the same temp table. CREATE TABLE and then insert into.

That's not the problem though.

Firstly, rather just use the inserted/deleted tables directly, don't spend the time creating and populating temp tables, triggers need to be as light-weight as possible.

Second, this line assumes there's only one row in inserted/deleted.

Set @job_order = Rtrim(Ltrim((SELECT job_order FROM #trig)))

What's going to happen when the operation affects multiple rows and your assumption of one row in inserted/deleted is no longer true?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Further to Gail's comments, Dwain Camps wrote this article [/url]just for you. Enjoy.

^{“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

raza.qadri Mr or Mrs. 500 Points: 554 More actions · Answer 3

job_order is primary key so always single value:

Set @job_order = Rtrim(Ltrim((SELECT job_order FROM #trig)))

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

raza.qadri (3/19/2015)
job_order is primary key so always single value:
...

Yes that's correct, however if more than one row are affected by an insert/update/delete, then the trigger tables will contain more than one row. Which row will be selected for your scalar variable?

^{“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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

raza.qadri (3/19/2015)
job_order is primary key so always single value:
Set @job_order = Rtrim(Ltrim((SELECT job_order FROM #trig)))

No.

If the operation that fired that trigger affected multiple rows, there will be multiple rows in inserted/deleted, hence not a single value.

Write your trigger to handle any number of rows in inserted/deleted.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

raza.qadri Mr or Mrs. 500 Points: 554 More actions · Answer 6

Set @job_order = Rtrim(Ltrim((SELECT job_order FROM #trig)))

i used this statement just for example

consider it can handle multiple values.

i dont want to handle code for inserted and deleted separately.

and i dont want to use create table as this would require whole schema for the table.

how can i do this

IF EXISTS (SELECT * FROM DELETED)
begin
select * into #trig from deleted
end
else
begin
Select * into #trig from inserted
end
SELECT job_order FROM #trig
drop table #trig

SELECT job_order FROM #trig

by using it this way i can get job_orders for deleted and inserted in the required manner, as my priority is if deleted contains recordsets and inserted is empty then the user deleted records, and if delete and inserted both contains records then user updated , and lastly if on deleted contains records the user deleted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You have to use CREATE TABLE or preferably deal specifically with inserted and/or deleted directly.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 8

select job_order

into #trig

from deleted

UNION ALL

Select job_order

from inserted

end

SELECT job_order FROM #trig

drop table #trig

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

ChrisM@Work (3/19/2015)
Further to Gail's comments, Dwain Camps wrote this article [/url]just for you. Enjoy.

I did? I mean, I did!

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