Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Oracle trigger to SQL trigger Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 3:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:55 AM
Points: 179, Visits: 387
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
Posted Tuesday, March 5, 2013 4:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 12,916, Visits: 32,080
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
Posted Tuesday, March 5, 2013 6:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 4,356, Visits: 6,189
Lowell (3/5/2013)
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.........


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 »

Add to briefcase

Permissions Expand / Collapse