Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Join Predicate Expand / Collapse
Author
Message
Posted Friday, December 14, 2007 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 1,186, Visits: 1,973
The code shows a clear cut example of why columns in a query should always be qualified. I.e., ALWAYS use aliases!

I got burnt badly with a DELETE statement in a trigger that was using an unqualified reference to the "DELETED" table with the end result being that more rows than what was intended was being deleted.

Using an alias on the table and then qualifing the column will shake out any coding errors, typos, etc. during the parsing phase of the query execution.

Example delete trigger:
DELETE FROM Foo
WHERE FooBar IN (SELECT d.FooBar FROM deleted d)




(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #433344
Posted Friday, December 14, 2007 7:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
I don't know about the unqualified reference, but I do know that the 'deleted' temp table frequently contains rows that are NOT really being deleted, like during updates.

Why wouldn't you have used a foreign key with cascaded deletes instead?

Tom


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #433349
Posted Monday, December 17, 2007 5:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
Tejinder Arora (12/13/2007)
The answer you have posted is wrong
I tried it on SQL server and it says
"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MIDDLE'."

The only way to get this error is to put an alias on the Customer table. If you use the query as written, 'MIDDLE' is taken as the alias name and no error occurs.


Derek
Post #433839
Posted Monday, December 17, 2007 9:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 1,186, Visits: 1,973
tgarth (12/14/2007)

Why wouldn't you have used a foreign key with cascaded deletes instead?


I generally do. Triggers are only an exception to the rule. There are certain times when a trigger is necessary due to the application and/or the data model.

Examples:
- SQL Server 2000 doesn't support ON DELETE SET NULL
- Often times in a hierarchical structure (i.e., parent -> child relationships) you'll get burnt as the FK constraint is checked at each row deletion. SQL Server doesn't support "deferrable" (check at the end of the transaction) constraint checking like Oracle does.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #433930
Posted Friday, December 28, 2007 4:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 12:57 AM
Points: 213, Visits: 97
Nice twisted question, people had to think once again, i get it wrong although i was knowing that no middle join is there, but do not beyond that it can be alias...


Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
Post #436982
Posted Friday, May 30, 2008 4:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 26, 2010 7:19 AM
Points: 70, Visits: 13
it is working in sql 2005 also...
Have you used the same query OR changed table names?
Post #508959
Posted Wednesday, September 21, 2011 1:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:45 AM
Points: 46, Visits: 47
Nice Question
Post #1178472
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse