help with trigger oracle to mssql conversion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with trigger oracle to mssql conversion

Author

Topic

jgn1013
Starting Member

24 Posts

Posted - 2009-09-01 : 08:29:08

oracle trigger:

table1
xvalue--------yvalue
123456 897654


create trigger trigger_sample_ORACLE
before insert
on table1
reference new as new
for each row

declare v_xvalue int

SELECT max(xvalue) INTO v_xvalue FROM table1
WHERE yvalue = :NEW.yvalue AND xvalue = :NEW.xvalue;

This is what I think the conversion will look like


CREATE TRIGGER trigger_sample_MSSQL
  ON table1
 AFTER INSERT
AS
   DECLARE @v_xvalue int,
    @v_yvalue int

  BEGIN
  
   SELECT v_xvalue = MAX(xvalue) FROM table1 
   WHERE yvalue =  @v_yvalue  AND xvalue = @v_xvalue

  END
GO

TIA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 08:36:04

I guess it would be something like this:

CREATE TRIGGER trigger_sample_MSSQL
  ON table1
 AFTER INSERT
AS
   DECLARE @v_xvalue int

  BEGIN
  
   SELECT @v_xvalue = MAX(xvalue) FROM table1 t 
   left join inserted i on t.key = i.key
   WHERE t.yvalue =  i.yvalue  AND t.xvalue = i.xvalue

  END
GO

Where key is the primary key column of the table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jgn1013
Starting Member

24 Posts

Posted - 2009-09-01 : 08:43:45

thanks..I don't see a primary key on this table.

How about this??

SELECT @v_xvalue = max(xvalue) from
table1
where yvalue = (select yvalue from inserted) and
xvalue = (select xvalue from inserted)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources