SQL 7 vs. 2000 issue -trigger and nulls
From: Bill Polewchak (pchak_at_troopone.net)
Date: 12/22/04
- Next message: we7313: "Execution plan question"
- Previous message: Hugo Kornelis: "Re: newbie SP question"
- Next in thread: Hugo Kornelis: "Re: SQL 7 vs. 2000 issue -trigger and nulls"
- Reply: Hugo Kornelis: "Re: SQL 7 vs. 2000 issue -trigger and nulls"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 22 Dec 2004 11:34:54 -0800
We're trying to dump our remaining v7 SQL server and update to 2000.
We're having trouble with a trigger updating some tables.
At the beginning, it has this statement:
COMMIT TRANSACTION --This unlocks the Lot table so I can update UDAs,
etc...
BEGIN TRANSACTION
This is what throws the trigger off. I created a "play" table with an
Update trigger, and when I added this statement at the beginning, my
trigger stopped working (this is all on the new server). I took these
two lines out of Lot's Update trigger (again on the new server), ran an
Update query, and the trigger populated all the fields correctly. Does
anybody know what it means about unlocking the Lot table in order to
update? Do we need this in SQL2000 or can we take it out, or do we need
to replace it by something?
CREATE TRIGGER trLot_U ON dbo.Lot
FOR UPDATE
AS
DECLARE
@rows int,
@nRunsToSlit int,
@SiteID varchar(8),
@SalesOrder varchar(15),
@Lot varchar(40)
SELECT @rows = @@rowcount
IF @rows=0
RETURN
--THE NEXT 2 LINES CAUSE THE PROBLEM!
COMMIT TRANSACTION --This unlocks the Lot table so I can update UDAs,
etc...
BEGIN TRANSACTION
SELECT @nRunsToSlit = COUNT(DISTINCT RunNumber)
FROM SlitRollDetail srd, inserted i
WHERE srd.LotID = i.LotID
AND srd.Completed = 'N'
AND srd.Disposition = 'NONE'
UPDATE Lot
SET Lot.PrimaryUOM='MSI'
WHERE Lot.LotID IN (SELECT i.LotID FROM inserted i)
/*****************Add Check for site id*************/
Select @SalesOrder = substring(LotID, 1, CHARINDEX('-', LotID)-1),
@SiteID = SiteID
from inserted
IF @SiteID = NULL or @SiteID = ''
select @SiteID = SiteID
from SalesOrder
where SalesOrder = @SalesOrder
UPDATE Lot
SET SiteID = @SiteID
WHERE Lot.LotID IN (SELECT i.LotID FROM inserted i)
/******************end fix***********************/
UPDATE Lot
SET Lot.CommonCarrier=
CASE
WHEN Lot.LotID LIKE 'E%' THEN 'EXACT ORDER'
WHEN Updates.Carrier<>'' THEN Updates.Carrier
ELSE 'NONE'
END
FROM (SELECT i.LotID, S.Carrier
FROM inserted i, SalesOrder S
WHERE i.LotID LIKE S.SalesOrder+'%' OR i.LotID LIKE 'E%') AS
Updates
WHERE Lot.LotID=Updates.LotID
UPDATE Lot
SET Lot.CommonWidth=Updates.CommonWidth
FROM (SELECT WOI.LotID AS LotID, MAX(WOI.RollWidth) AS CommonWidth
FROM WorkOrderItem AS WOI,
(SELECT WOI.LotID, MAX(WOI.RollQuantity) AS MaxQty
FROM WorkOrderItem AS WOI, inserted
WHERE WOI.LotID=inserted.LotID
GROUP BY WOI.LotID) AS MaxRolls
WHERE MaxRolls.MaxQty = WOI.RollQuantity
AND MaxRolls.LotID = WOI.LotID
GROUP BY WOI.LotID) AS Updates
WHERE Lot.LotID=Updates.LotID
UPDATE Lot
SET Lot.EUs = Round(0.0077*Lot.OrderLength*@nRunsToSlit/3.0
+ 9.6300*@nRunsToSlit
+ 0.8100*Lot.NumberUp
+ 5.0000, 1)
FROM inserted
WHERE Lot.LotID=inserted.LotID
SELECT @Lot = (SELECT i.LotID FROM inserted i)
IF @Lot like 'E%%'
BEGIN
INSERT INTO WorkOrderDetailTemp(LotID) SELECT @Lot
END
Any ideas?
Likewise, we had a problem with this:
In SQL 7.0: substring ("yes", 0, 255) returns NULL, substring("yes", 1,
255") returns "yes".
In SQL 2000: both statements return "yes" (which, to be honest, makes
perfect sense to me).
select..
..
case
when @nDBRollNumber>=@MaximumRollNumber then 'yes'
else 'no'
end
This would replace the existing
select..
..
isnull(substring('yes', charindex(convert(varchar,@MaximumRollNumber)
,convert(varchar,@nDBRollNumber)),255),'no')
I wasn't expecting any real problems with SQL 7 code problems in SQL
2000.
thanks!
pchak
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: we7313: "Execution plan question"
- Previous message: Hugo Kornelis: "Re: newbie SP question"
- Next in thread: Hugo Kornelis: "Re: SQL 7 vs. 2000 issue -trigger and nulls"
- Reply: Hugo Kornelis: "Re: SQL 7 vs. 2000 issue -trigger and nulls"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
