September 4, 2010 at 1:59 am
I saw this the other day:
Select * FROM TableA
INNER JOIN TableB ON TableA.ProductID -1 = TableB.ProductID -1
When I ran the query it returned 37 rows.
If I took the '-1's out;
Select * FROM TableA
INNER JOIN TableB ON TableA.ProductID = TableB.ProductID
it returned 37 rows.
Why did someone put '-1' after the fields the join is done on?
September 4, 2010 at 3:38 am
It means what it means in any mathematical expression. Subtract 1 from the value.
Can't tell the reason, but it's so that Product ID 2 from one table joins to Product ID 3 from the other.
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
September 4, 2010 at 4:17 am
Oops! I've been doing Maths since I was 5 and it never occurred to me it meant 'join on the ProductIDs minus 1'.
But, the code is in a trigger so it's being used like this:
ALTER TRIGGER [dbo].[trgtblProduct] ON [dbo].[tblProduct]
FOR UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE @Uservarchar(255), @EditTime datetime
set @User = suser_sname()
set @EditTime = getdate()
INSERT tblProduct_Audit
SELECT
AuditType = CASE WHEN I.ActionID IS NULL THEN 'D' ELSE 'U' END,
AuditDate = GetDate(),
I.ProductID,
I.BatchID
tblBatches.BatchDate
I.Note,
@User,
I.LocationID
FROM
deleted D
Inner join tblBatches on D.BatchID = tblBatches.BatchID
LEFT OUTER JOIN inserted I ON I.ProductID -1 = D.ProductID -1
I don't get this. If it's a Delete the Inserted table won't exist - yet the Select is from the Inserted table. And wouldn't the Inserted and Delete table only contain one row - so how can the join be done on the ProductID less 1.
Thanks for your help.
September 4, 2010 at 5:11 am
sku370870 (9/4/2010)
Oops! I've been doing Maths since I was 5 and it never occurred to me it meant 'join on the ProductIDs minus 1'.But, the code is in a trigger so it's being used like this:
ALTER TRIGGER [dbo].[trgtblProduct] ON [dbo].[tblProduct]FOR UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE @Uservarchar(255), @EditTime datetime
set @User = suser_sname()
set @EditTime = getdate()
INSERT tblProduct_Audit
SELECT
AuditType = CASE WHEN I.ActionID IS NULL THEN 'D' ELSE 'U' END,
AuditDate = GetDate(),
I.ProductID,
I.BatchID
tblBatches.BatchDate
I.Note,
@User,
I.LocationID
FROM
deleted D
Inner join tblBatches on D.BatchID = tblBatches.BatchID
LEFT OUTER JOIN inserted I ON I.ProductID -1 = D.ProductID -1
I don't get this. If it's a Delete the Inserted table won't exist - yet the Select is from the Inserted table. And wouldn't the Inserted and Delete table only contain one row - so how can the join be done on the ProductID less 1.
Thanks for your help.
I'm not sure I understand this trigger. If you run the query
SELECT TOP 100 *
FROM tblProduct_Audit
WHERE AuditType = 'D'
how many of the fields are actually populated?
September 4, 2010 at 7:11 am
Thanks for your reply.
The query you suggested will return nothing because, as far as I can see, there is no way to delete a product within the system and, in fact, deleting a product will cause lots of things to fall over.
It's a system I've been asked to take a look at and some of the triggers make no sense to me. (But I'm no expert.)
I need to set up a dummy table and see what happens. The only reason I am querying it really is that the bloke who set the system up originally is a seriously switched on developer - but there has been a bit of a wally working on it since so I don't know whose handiwork it is.
September 4, 2010 at 7:25 am
sku370870 (9/4/2010)
Oops! I've been doing Maths since I was 5 and it never occurred to me it meant 'join on the ProductIDs minus 1'.But, the code is in a trigger so it's being used like this:
ALTER TRIGGER [dbo].[trgtblProduct] ON [dbo].[tblProduct]FOR UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
DECLARE @Uservarchar(255), @EditTime datetime
set @User = suser_sname()
set @EditTime = getdate()
INSERT tblProduct_Audit
SELECT
AuditType = CASE WHEN I.ActionID IS NULL THEN 'D' ELSE 'U' END,
AuditDate = GetDate(),
I.ProductID,
I.BatchID
tblBatches.BatchDate
I.Note,
@User,
I.LocationID
FROM
deleted D
Inner join tblBatches on D.BatchID = tblBatches.BatchID
LEFT OUTER JOIN inserted I ON I.ProductID -1 = D.ProductID -1
I don't get this. If it's a Delete the Inserted table won't exist - yet the Select is from the Inserted table. And wouldn't the Inserted and Delete table only contain one row - so how can the join be done on the ProductID less 1.
Thanks for your help.
Whoever did this is trying to pull a product from the deleted table and match it to the product from the inserted table that is one ID just before it.
Something in the system must delete it, or there is funky behavior. It must have been for a specific bug or process flow related to how they are inserting records into the product table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2010 at 10:55 am
Ok, that's totally pointless.
With the -1 on both sides of the join it's as if they weren't there. Just like Algebra, if you add (or subtract) the same term from both sides it doesn't change the meaning of the equation.
5 + 4 = 9
5 + 4 -1 = 9 - 1
Something tells me the person who wrote that trigger doesn't know what he was doing.
Inserted table will exist on a delete operation, it'll just be empty. Insert is populated for insert and update operations, deleted is populated for updates and deletes. Both will always exist for the duration of the trigger.
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
September 4, 2010 at 11:49 am
Precisely, and moreover the way the trigger is written for a DELETE, when the Audit table is populated, the fields ProductID, BatchID, Note, LocationID will always end up as NULL because they are referencing the INSERTED table via a LEFT JOIN and the INSERTED table is empty! Surely these fields should be pointing at the DELETED table not INSERTED, at least for the DELETE action.
September 5, 2010 at 3:26 am
If I had to guess, I'd say this was written and tested as an update trigger and either the delete added later or just never tested.
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
September 5, 2010 at 4:08 am
Thanks for your replies.
If the Inserted and Deleted tables always exist for the duration of a trigger ... if you write a trigger for INSERT, UPDATE and DELETE - what is the right way to determine whether an insert, update or delete has happened?
In a question on Triggers recently I posted that I did this in a trigger to determine whether it was an Update, Insert or Delete ...
DECLARE @InsertID int
DECLARE @DeleteID int
SELECT @InsertID = PostPublicationID FROM Inserted
SELECT @DeleteID = PostPublicationID FROM Deleted
IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D'
IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I'
IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U'
... and someone said it was a bit flaky. What's the best way to do it?
September 5, 2010 at 5:28 am
sku370870 (9/5/2010)
Thanks for your replies.If the Inserted and Deleted tables always exist for the duration of a trigger ... if you write a trigger for INSERT, UPDATE and DELETE - what is the right way to determine whether an insert, update or delete has happened?
In a question on Triggers recently I posted that I did this in a trigger to determine whether it was an Update, Insert or Delete ...
DECLARE @InsertID intDECLARE @DeleteID int
SELECT @InsertID = PostPublicationID FROM Inserted
SELECT @DeleteID = PostPublicationID FROM Deleted
IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D'
IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I'
IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U'
... and someone said it was a bit flaky. What's the best way to do it?
You don't actually need the variables @InsertID or @DeleteID at all. You can just do
IF EXISTS (SELECT * FROM INSERTED)
IF EXISTS (SELECT * FROM DELETED)
SET @AuditType = 'U'
ELSE
SET @AuditType = 'I'
ELSE
IF EXISTS (SELECT * FROM DELETED) SET @AuditType = 'D'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply