Re: Converting an Oracle Trigger Script into SQL Server
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 06/23/04
- Next message: Miroo_news: "Re: UDT has default value but column hasn't, why?"
- Previous message: Wayne Snyder: "Re: Primary Key indexing"
- In reply to: Todd Taylor: "Re: Converting an Oracle Trigger Script into SQL Server"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Jun 2004 08:18:37 -0400
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, Charlotte, 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 "Todd Taylor" <toddnospamtaylor@charter.net> wrote in message news:10di1lf1orq694e@corp.supernews.com... > 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 > > > "Raj Kumar" <raj_1512@yahoo.com> wrote in message > news:e5iLEhMWEHA.2816@TK2MSFTNGP11.phx.gbl... > > > > > > > > > > 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! > >
- Next message: Miroo_news: "Re: UDT has default value but column hasn't, why?"
- Previous message: Wayne Snyder: "Re: Primary Key indexing"
- In reply to: Todd Taylor: "Re: Converting an Oracle Trigger Script into SQL Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
