Am I duplicating using IF and WHERE in a trigger? 1

[Vulton]

Here is the SQL trigger. It seems to me that checking in the inserted table in the IF statement means I don't need it in the WHERE clause. Any help simplifying this would be appreciated.

CREATE TRIGGER TriggerInstallationComplete ON [mpadmin].[TblLotVisitsReason] 
FOR INSERT
AS
IF EXISTS (SELECT inserted.LVR_Reason From inserted WHERE inserted.LVR_Reason = '14')
BEGIN
UPDATE dbo.TblLotEvents 
SET dbo.TblLotEvents.LE_OrderParts = inserted.LVR_TimeStamp
FROM dbo.TblLotEvents INNER JOIN (mpadmin.TblLotVisits INNER JOIN inserted
ON mpadmin.TblLotVisits.LV_ID = inserted.LVR_ID)
ON dbo.TblLotEvents.LE_JHL = mpadmin.TblLotVisits.LV_PJHL
WHERE (((mpadmin.TblLotVisits.LV_PJHL)=dbo.TblLotEvents.LE_JHL) AND ((inserted.LVR_Reason)="14"));
END

IF EXISTS (SELECT inserted.LVR_Reason From inserted WHERE inserted.LVR_Reason = '24')
BEGIN
UPDATE dbo.TblLotEvents 
SET dbo.TblLotEvents.LE_TurnOverlot = inserted.LVR_TimeStamp
FROM dbo.TblLotEvents INNER JOIN (mpadmin.TblLotVisits INNER JOIN inserted
ON mpadmin.TblLotVisits.LV_ID = inserted.LVR_ID)
ON dbo.TblLotEvents.LE_JHL = mpadmin.TblLotVisits.LV_PJHL
WHERE (((mpadmin.TblLotVisits.LV_PJHL)=dbo.TblLotEvents.LE_JHL) AND ((inserted.LVR_Reason)="24"));
END

Thanks,
Mark

 

[PWise]

this should do it

CREATE TRIGGER TriggerInstallationComplete ON [mpadmin].[TblLotVisitsReason] 
FOR INSERT

UPDATE dbo.TblLotEvents 
SET dbo.TblLotEvents.LE_OrderParts = inserted.LVR_TimeStamp
FROM dbo.TblLotEvents 
INNER JOIN (mpadmin.TblLotVisits 
INNER JOIN inserted
ON mpadmin.TblLotVisits.LV_ID = inserted.LVR_ID)
ON dbo.TblLotEvents.LE_JHL = mpadmin.TblLotVisits.LV_PJHL
AND inserted.LVR_Reason in ("14","24");

 

[Vulton]

Wow fast response thanks pwise,
Only thing is the SET is seting different fields based on the inserted.LVR_Reason data.

14 = SET dbo.TblLotEvents.LE_OrderParts = inserted.LVR_TimeStamp

24 = SET dbo.TblLotEvents.LE_TurnOverlot = inserted.LVR_TimeStamp

Thanks,
Mark

 

[PWise]

try this

UPDATE dbo.TblLotEvents 
SET 
dbo.TblLotEvents.LE_OrderParts = case inserted.LVR_Reason when "14" then inserted.LVR_TimeStamp else dbo.TblLotEvents.LE_OrderParts end ,--14
dbo.TblLotEvents.LE_TurnOverlot = case inserted.LVR_Reason when "24" then inserted.LVR_TimeStamp else dbo.TblLotEvents.LE_TurnOverlot end --24
FROM dbo.TblLotEvents 
INNER JOIN (mpadmin.TblLotVisits 
INNER JOIN inserted
ON mpadmin.TblLotVisits.LV_ID = inserted.LVR_ID)
ON dbo.TblLotEvents.LE_JHL = mpadmin.TblLotVisits.LV_PJHL
AND inserted.LVR_Reason in ("14","24");

 

[Vulton]

Fantastic Pwise,
Thanks very much for the help. It works like a charm and is easy to read and add more case's to.

Thanks again,
Mark