I would like to know on how to convert Oracle triggers into SQL Server triggers

vsnjm48y 于 12个月前发布在 Oracle

关注(0)|答案(2)|浏览(186)

As I understand, SQL SERVER Triggers does not support FOR EACH ROW. Also I am aware that you have to use inserted tables and deleted tables. Other than that, I have no clue how to write SQL Server triggers. They look so different. Can some help please?

Below is the code for Oracle Triggers

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;

Below is what I have written so far. I know I am using the inserted tables wrong

IF l_status_name = 'Regular' 
THEN 
 :New.TotalCost := 0.90 * :New.TotalCost;
END IF;
END;

create TRIGGER Ten_Percent_Discount  
ON Customer
FOR INSERT ,UPDATE  
AS 
DECLARE  C_Passengers CURSOR FOR
SELECT StatusLevel
FROM   Customer
WHERE  CustomerID = inserted.CustomerID

Thanks for all the help in advance.

Table structure for customer

Table structure for Order

sql-server

来源：https://stackoverflow.com/questions/36565494/i-would-like-to-know-on-how-to-convert-oracle-triggers-into-sql-server-triggers

2条答案

按热度按时间

iih3973s1#

Below answer is only for reference purpose that you can use to build gradually towards final solution:

create table dbo.customer
(
customerid varchar(10),
firstname nvarchar(50),
statuslevel varchar(50)
)
go
create table dbo.customerorder
(
orderid varchar(10),
totalprice numeric(5,2),
productid varchar(10),
customerid varchar(10)
)
go
go
create trigger dbo.tr_customer on dbo.customer for insert,update
as
begin
    update co
    set co.totalprice = .9*co.totalprice
    from dbo.customerorder co
    inner join inserted i
    on co.customerid = i.customerid
    where i.statuslevel = 'Standard' 
end
go

--test for above code
insert into dbo.customer values (1,'jayesh','')
insert into dbo.customerorder values (1,500.25,1,1)
insert into dbo.customerorder values (1,600.25,2,1)
select * from dbo.customer
select * from dbo.customerorder

update dbo.customer set statuslevel = 'Standard' where customerid = 1

select * from dbo.customer
select * from dbo.customerorder

But what I am pretty sure is that when customer is created for the first time, there will not be any orders to apply discounts on, so you will certainly need UPDATE Trigger as well.

赞(0）回复(0）举报 12个月前

t5fffqht2#

Hello another approach can be in this way:

CREATE  TABLE Passengers (
    PassengerNumber INT PRIMARY KEY,
    StatusName NVARCHAR(50)
);

INSERT INTO Passengers (PassengerNumber, StatusName)
VALUES 
    (1, 'Regular'),
    (2, 'Regular'),
    (3, 'Frequent'),
    (4, 'Regular');

CREATE TABLE Bookings (
    BookingID INT PRIMARY KEY,
    Passengers_PassengerNumber INT,
    TotalCost DECIMAL(10, 2),
    CONSTRAINT FK_PassengerNumber FOREIGN KEY (Passengers_PassengerNumber) REFERENCES Passengers(PassengerNumber)
);

INSERT INTO Bookings (BookingID, Passengers_PassengerNumber, TotalCost)
VALUES
    (101, 1, 100.00),
    (102, 2, 150.00),
    (103, 3, 200.00),
    (104, 4, 120.00);

CREATE or alter TRIGGER Ten_Percent_Discount
ON Bookings
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @l_status_name NVARCHAR(50);

    SELECT @l_status_name = P.StatusName
    FROM Passengers P
    WHERE P.PassengerNumber = (SELECT Passengers_PassengerNumber FROM inserted);

    IF @l_status_name = 'Regular'
    BEGIN
        UPDATE Bookings
        SET TotalCost = 0.9 * TotalCost
        WHERE BookingID = (SELECT BookingID FROM inserted);
    END
END;

Hope it helps.

赞(0）回复(0）举报 12个月前

我来回答

I would like to know on how to convert Oracle triggers into SQL Server triggers

2条答案

相关问题

热门标签

最新问答