April 12, 2016 at 12:22 am
Hi All,
I want to convert a trigger I wrote in Oracle SQL to T-SQL. Below is the Oracle trigger
create or replace TRIGGER Ten_Percent_Discount
BEFORE INSERT OR UPDATE ON Bookings
FOR EACH ROW
DECLARE CURSOR C_Passengers IS
SELECT StatusName
FROM Passengers
WHERE PassengerNumber = :NEW.Passengers_PassengerNumber;
l_status_name Passengers.StatusName%TYPE;
BEGIN
OPEN C_Passengers;
FETCH C_Passengers INTO l_status_name;
CLOSE C_Passengers;
IF l_status_name = 'Regular'
THEN
:New.TotalCost := 0.90 * :New.TotalCost;
END IF;
END;
Below is what I have written so far. I am not sure how to use inserted tables in SQL server. Can you please help?
create TRIGGER Ten_Percent_Discount
ON Bookings
FOR INSERT ,UPDATE
AS
DECLARE C_Passengers CURSOR FOR
SELECT StatusLevel
FROM Customer
WHERE CustomerID = :NEW.Passengers_PassengerNumber;
Thanks
April 12, 2016 at 5:22 am
Start by throwing the cursor away. Triggers should be written in a set-based manor to handle any number of rows in inserted/deleted, not iterate through them. Cursors should be avoided in T-SQL
I'm guessing, from reading the Oracle code, that what you want is:
CREATE TRIGGER Ten_Percent_Discount ON Bookings
AFTER INSERT, UPDATE
AS
UPDATE Customer
SETTotalCost = 0.90 * TotalCost
WHERE StatusName = 'Regular'
AND CustomerID IN (SELECT CustomerID FROM inserted); -- check that the row is one that's just been inserted/updated
GO
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply