Row level trigger

• sreenath

  SSC Enthusiast

  Points: 184

  More actions

  March 24, 2004 at 11:30 pm

  #107490

  Hi all

   I am doing a Db porting project, the code in plsql has to be converted tsql.I have a trigger written for  oracle i need to convert it to tsql.

  CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE

  BEFORE INSERT

  ON TARGET_OBJECTIVE

  REFERENCING OLD AS OLD NEW AS NEW

  FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))

  DECLARE

  seq_id NUMBER;

  BEGIN

  SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;

  :new.TARGET_OBJECTIVE_ID := seq_id;

  END;

  Any suggestion/comments?. Thanks in advance

  Regards

  Sreenath

   

• Steve Hicks

  SSC Enthusiast

  Points: 101

  More actions

  March 25, 2004 at 2:05 am

  #500379

  Hi,

  Theres a big section in the SQL server resouce kit about migrating from oracle to sql server.

  I believe the whole thing is online at microsoft.com

  Hope this helps.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 25, 2004 at 5:17 am

  #500399

  What is the outcome of this TRIGGER, looks like either you are getting the sequence id or setting it my Oracle Kung FU ain't the best and I don't have time right now to break out the bible (Oracle Bible that is).

• iamnotgreg

  Grasshopper

  Points: 17

  More actions

  March 25, 2004 at 9:27 am

  #500455

  there's no such thing as a "sequence" object in SQLS, so you'll have to take a different approach.  I would suggest altering the application that inserts to this table so that you always send a null id and create the target_object table in sqls with an IDENTITY(1,1) attribute for the [id] column.  If this is too much work, here's the trigger code.

  CREATE TRIGGER tgr_target_object_id

   ON target_object

   FOR INSERT

  AS

  IF inserted.[id] IN (null, 0)

   DECLARE @max_id INT

   SELECT @max_id = MAX([id]) FROM target_object

   UPDATE target_object

   SET [id] = @max_id + 1

  FROM inserted i

  Or something quite like it.  Check BOL for syntax.  Not too sure about that UPDATE... FROM statement. 

  This was my first post.  How'd I do?

  
  "Sono daverro allergico alle anguille sotto aceto."

• RD Francis

  SSCarpal Tunnel

  Points: 4867

  More actions

  March 26, 2004 at 11:14 am

  #500712

  Oracle uses SEQUENCE to generate new, unique ID numbers for rows.

  SQL Server most commonly uses the IDENTITY feature to accomplish this. Look in SQL Books Online for details. You can declare one integer-type column in any table as an IDENTITY column, and it will automatically be populated with a value when a row is inserted.

  
  R David Francis