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
rutgermonsAsked:
Who is Participating?
 
dportasCommented:
Try the following.

I see some problems with both the other suggested solutions. JimBrandley's version is not valid syntax in SQL Server. Dbbishop's trigger will fail to detect the relevant condition properly if multiple rows are updated. In SQL Server, triggers always fire once per statement not per row so you must allow for the cases where 0,1 or more rows are updated simultaneously.

Hope this helps.

CREATE TRIGGER UpdateTwoStatus ON two
FOR UPDATE
AS

IF EXISTS (
 SELECT *
 FROM inserted i
 JOIN deleted d
  ON i.key_col = d.key_col
 WHERE i.status = 'CLOSE'
  AND d.status = 'REQ')
BEGIN
    RAISERROR ('Cannot be Closed when in REQ status', 16, 1);
    ROLLBACK;
END;

IF EXISTS (
 SELECT *
 FROM inserted i
 JOIN deleted d
  ON i.key_col = d.key_col
 WHERE i.status = 'CAN'
  AND d.status = 'APPR')
BEGIN
    RAISERROR ('Cannot be Cancelled', 16, 1);
    ROLLBACK;
END;

GO
0
 
JimBrandleyCommented:
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
0
 
dbbishopCommented:
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



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.