asked on

convert trigger from oracle to sql server

folks

how do i convert this trigger from oracle to sql?

CREATE OR REPLACE TRIGGER UpdateTwoStatus
BEFORE UPDATE ON two FOR EACH ROW

BEGIN
IF :old.status = 'REQ' AND :new.status = 'CLOSE' THEN
RAISE_APPLICATION_ERROR(-20000, 'Cannot be Closed when in REQ status');
ELSIF :old.status = 'APPR' AND :new.status = 'CAN' THEN
RAISE_APPLICATION_ERROR(-20000, 'Cannot be Cancelled');
END IF;
END UpdateTwoStatus;
/

brgds

r

JimBrandley

You closed the other question before I could post this. It's for SQL Server:
CREATE TRIGGER UpdateTwoStatus
ON two AFTER UPDATE AS

BEGIN
IF Old.status = 'REQ' AND New.status = 'CLOSE'
BEGIN
PRINT 'Cannot be Closed when in REQ status'
ROLLBACK
END
ELSE
BEGIN
IF Old.status = 'APPR' AND New.status = 'CAN'
BEGIN
PRINT 'Cannot be Cancelled'
ROLLBACK
END
END
END UpdateTwoStatus
GO

Jim

D B

CREATE TRIGGER UpdateTwoStatus ON two
FOR UPDATE
AS
DELCARE @oldStatus varchar(10) -- of whatever the length of the status field is
DELCARE @newStatus varchar(10) -- of whatever the length of the status field is

SELECT @oldStatus = status FROM DELETED
SELECT @newStatus = status FROM INSERTED

IF (@oldStatus = 'REQ') AND (@newStatus = 'CLOSE')
BEGIN
RAISERROR ('Cannot be Closed when in REQ status'., 16, 1)
ROLLBACK TRANS
END
ELSEIF @oldStatus = 'APPR' AND @newStatus = 'CAN'
BEGIN
RAISERROR ('Cannot be Canceled'., 16, 1)
ROLLBACK TRANS
END

ASKER CERTIFIED SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial