Oracle trigger to SQL trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL Server 2012 - T-SQL » Oracle trigger to SQL trigger

Oracle trigger to SQL trigger

Rate Topic

Display Mode

Topic Options

Author

Message

leon.booij

Posted Tuesday, March 05, 2013 3:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:55 AM
Points: 30, Visits: 222

Hi,

Got a Oracle script with a trigger in it and I want to have it as a SQL trigger script
Can anyone help me with this

CREATE OR REPLACE TRIGGER MESSAGES_INS BEFORE INSERT ON MESSAGES
FOR EACH ROW
BEGIN
IF :NEW.id IS NULL THEN
SELECT SEQ_MESSAGE_ID.nextval INTO :NEW.id FROM DUAL;
END IF;
END;
/
ALTER TRIGGER MESSAGES_INS ENABLE;

Thanks

Post #1426691

Lowell

Posted Tuesday, March 05, 2013 4:44 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:31 PM
Points: 11,645, Visits: 27,736

That trigger emulates what a column with an identity does... so you can simply define the column as having an identity and skip the trigger completely.

Create table tbname (colname int identity(1,1) not null.........

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1426748

TheSQLGuru

Posted Tuesday, March 05, 2013 6:43 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 6:58 PM
Points: 3,581, Visits: 5,125

Lowell (3/5/2013)

Given that this is a SQL 2012 forum, the OP can also use the new SEQUENCE object, which could be the only feature that gives the exact same implementation. The Oracle sequence could well be something other than int from 1 to N increment by 1.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #1426796

« Prev Topic | Next Topic »

Permissions