Convert oracle trigger to sqlserver trigger

Question

Post reply

Convert oracle trigger to sqlserver trigger

vision59

SSCrazy

Points: 2601
More actions
April 25, 2007 at 4:24 pm

#178045

Can anyone help me in converting this trigger to TSql

CREATE OR REPLACE TRIGGER tgk_drug

AFTER INSERT ON dbactivity

FOR EACH ROW

DECLARE

runtime VARCHAR2 (129);

BEGIN

BEGIN

SELECT d1.drug_value

INTO runtime FROM dbsreg d1,dbsreg d2

WHERE d1.drug_name = 'TIME LAUNCH'

AND EXISTS (SELECT d3.drug_serial from dbsreg d3

WHERE d3.drug_name = '1'

AND d3.drug_serial=d1.drug_parent

AND EXISTS (SELECT d4.drug_serial from dbsreg d4

WHERE d4.drug_name='PICK'

AND d4.drug_serial=d3.drug_parent))

AND d1.drug_parent=d2.drug_parent

AND d2.drug_name='CONNECTION'

AND d2.drug_value='S';

EXCEPTION WHEN others THEN return;

END;

if (

(:new.dbact_fromdate = :new.dbact_date OR :new.dbact_todate = :new.dbact_date) OR

((:new.dbact_fromdate = :new.dbact_date + 1 OR :new.dbact_todate = :new.dbact_date + 1 ) AND (:new.dbact_time > runtime ))

) then

insert into intstamp (ints_serial,ints_type,ints_id,ints_id_table,ints_date,ints_time,ints_action,ints_message,ints_seq,ints_error)

values(0, 'STDPICK' , :new.dbact_casenum, 'case' ,:new.dbact_date,:new.dbact_time, 'A' ,0,0,' ');

end if;

END;

Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Try this:

CREATE TRIGGER dbactivity_t_ia ON dbactivity FOR INSERT

as

set nocount on

set xact_abort on

IF 0 = ( select count(*) from inserted) RETURN

insert into intstamp

(ints_serial,ints_type,ints_id,ints_id_table

,ints_date,ints_time,ints_action,ints_message

,ints_seq,ints_error)

select 0, 'STDPICK' , inserted.dbact_casenum, 'case'

, inserted.dbact_date, inserted.dbact_time, 'A'

,0,0,' '

from inserted

WHERE ( inserted.dbact_fromdate between inserted.dbact_date and inserted.dbact_date + 1

OR inserted.dbact_todate between inserted.dbact_date and inserted.dbact_date + 1

AND EXISTS

(select 'a constant'

FROM dbsreg d1

join dbsreg d2

on d1.drug_parent = d2.drug_parent

WHERE inserted.dbact_time > d1.drug_value

AND d1.drug_name = 'TIME LAUNCH'

AND d2.drug_name = 'CONNECTION'

AND d2.drug_value = 'S'

AND EXISTS

(SELECT d3.drug_serial

from dbsreg d3

WHERE d3.drug_name = '1'

AND d3.drug_serial=d1.drug_parent

AND EXISTS

(SELECT d4.drug_serial from dbsreg d4

WHERE d4.drug_name='PICK'

AND d4.drug_serial=d3.drug_parent )

)))

go

SQL = Scarcely Qualifies as a Language

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

FYI MS has this app called "SQL Server Migration Assistant for Oracle V3.0" http://www.microsoft.com/downloads/details.aspx?FamilyId=0E06A04C-D0C3-4F31-B201-FBE7C25F32FB&displaylang=en

Maybe this can also convert to sql2000

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me