Converting an Oracle Trigger Script into SQL Server  
Author Message
MManry





PostPosted: Tue Jun 22 23:26:42 CDT 2004 Top

SQL Server Developer >> Converting an Oracle Trigger Script into SQL Server

Hi,

I want to convert an Oracle PL/SQL Script for a trigger into a SQL
Server 2000 T-SQL Script.
The Oracle script is as follows:


CREATE TRIGGER SET_GCDR_ENDTIMEKEY_TRIGGER
AFTER INSERT ON GCDR
FOR EACH ROW
BEGIN
INSERT INTO GCDR_ENDTIME VALUES (:NEW.ROWID, (:NEW.STTIME +
:NEW.DURATION) ) ;
END;
GO

Any help in creating an equivalent SQL Server 2000 code will greatly
appreciated.

Thanks,

Raj.

*** Sent via Devdex http://www.hide-link.com/ ***
Don't just participate in USENET...get rewarded for it!

SQL Server81  
 
 
Todd





PostPosted: Tue Jun 22 23:26:42 CDT 2004 Top

SQL Server Developer >> Converting an Oracle Trigger Script into SQL Server I think it will be something like:

create trigger SET_GCDR_ENDTIMEKEY_TRIGGER
on GCDR
for insert
as
begin
insert into GCDR_ENDTIME select ROWID, STTIME + DURATION from inserted
end
go

This is assuming that ROWID is a column in the table GCDR and that there are
only two non-identity columns in the table GCDR_ENDTIME.

However, if it is the Oracle ROWID for the row then there is no direct
equivalent. The best you can do is add an identity column (call it
GCDR_IDENTITY) to table GCDR and select its value instead of ROWID:

create trigger SET_GCDR_ENDTIMEKEY_TRIGGER
....
insert into GCDR_ENDTIME select GCDR_IDENTITY, STTIME + DURATION from
inserted
end
go

One more minor caveat...

This inserts all of the rows at one time into GCDR_ENDTIME instead of one at
a time. If this is not the desired outcome, then you will need to cursor
through the "inserted" table and do the inserts one at a time.

TNT




>
>
>
>
> Hi,
>
> I want to convert an Oracle PL/SQL Script for a trigger into a SQL
> Server 2000 T-SQL Script.
> The Oracle script is as follows:
>
>
> CREATE TRIGGER SET_GCDR_ENDTIMEKEY_TRIGGER
> AFTER INSERT ON GCDR
> FOR EACH ROW
> BEGIN
> INSERT INTO GCDR_ENDTIME VALUES (:NEW.ROWID, (:NEW.STTIME +
> :NEW.DURATION) ) ;
> END;
> GO
>
> Any help in creating an equivalent SQL Server 2000 code will greatly
> appreciated.
>
> Thanks,
>
> Raj.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
Wayne





PostPosted: Wed Jun 23 07:18:37 CDT 2004 Top

SQL Server Developer >> Converting an Oracle Trigger Script into SQL Server Another item to mention is the unit of measure for duration.... If STTime is
a datetime you may add an integer which is interpretted as days.

If duration is some other interval, you must use the dateadd function to add
any other unit of measure to a datetime field... Dateadd is documented in
Books on line...

For example if duration is in seconds

insert.... select....., dateadd(ss,duration, sttime) ...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, C****te, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org



> I think it will be something like:
>
> create trigger SET_GCDR_ENDTIMEKEY_TRIGGER
> on GCDR
> for insert
> as
> begin
> insert into GCDR_ENDTIME select ROWID, STTIME + DURATION from inserted
> end
> go
>
> This is assuming that ROWID is a column in the table GCDR and that there
are
> only two non-identity columns in the table GCDR_ENDTIME.
>
> However, if it is the Oracle ROWID for the row then there is no direct
> equivalent. The best you can do is add an identity column (call it
> GCDR_IDENTITY) to table GCDR and select its value instead of ROWID:
>
> create trigger SET_GCDR_ENDTIMEKEY_TRIGGER
> ....
> insert into GCDR_ENDTIME select GCDR_IDENTITY, STTIME + DURATION from
> inserted
> end
> go
>
> One more minor caveat...
>
> This inserts all of the rows at one time into GCDR_ENDTIME instead of one
at
> a time. If this is not the desired outcome, then you will need to cursor
> through the "inserted" table and do the inserts one at a time.
>
> TNT
>
>


> >
> >
> >
> >
> > Hi,
> >
> > I want to convert an Oracle PL/SQL Script for a trigger into a SQL
> > Server 2000 T-SQL Script.
> > The Oracle script is as follows:
> >
> >
> > CREATE TRIGGER SET_GCDR_ENDTIMEKEY_TRIGGER
> > AFTER INSERT ON GCDR
> > FOR EACH ROW
> > BEGIN
> > INSERT INTO GCDR_ENDTIME VALUES (:NEW.ROWID, (:NEW.STTIME +
> > :NEW.DURATION) ) ;
> > END;
> > GO
> >
> > Any help in creating an equivalent SQL Server 2000 code will greatly
> > appreciated.
> >
> > Thanks,
> >
> > Raj.
> >
> > *** Sent via Devdex http://www.hide-link.com/ ***
> > Don't just participate in USENET...get rewarded for it!
>
>