[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

convert trigger from oracle to sql server

Posted on 2007-08-11
3
Medium Priority
?
290 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:rutgermons
3 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19677465
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19678241
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
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 19678567
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question