April 25, 2007 at 4:24 pm
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
April 25, 2007 at 8:00 pm
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
April 26, 2007 at 12:16 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply