473,224 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

DTB
I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.
Jul 20 '05 #1
4 22165
You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"DTB" <ma****@mac.com> wrote in message
news:4a**************************@posting.google.c om...
I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

Jul 20 '05 #2
DTB
Thank you Dan! This works the way I want it to.

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message news:<Zf******************@newsread1.news.atl.eart hlink.net>...
You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"DTB" <ma****@mac.com> wrote in message
news:4a**************************@posting.google.c om...
I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

Jul 20 '05 #3

I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind



I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.



Originally posted by Dtb
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE BEFORE UPDATE trigger: CREATE TRIGGER myTRIGGER ON MYTABLE begin :new.DT := SYSDATE; if :new.NM is NULL then :new.NM := USER; end if; end myTRIGGER; It seems as though I have to jump through hoops in SQL Server AND I cannot come up with correct results. Here is a snippet from SQL SERVER (this is what I figured I needed to do after reading various articles,questions): CREATE TRIGGER myTRIGGER on THETABLE INSTEAD OF UPDATE AS SELECT * INTO #MYTABLE FROM INSERTED UPDATE #MYTABLE SET DT = GETDATE() UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL UPDATE THETABLE SET DT = (SELECT DT FROM #MYTABLE), NM = (SELECT NM FROM #MYTABLE) WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)


Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com
Jul 20 '05 #4
Milind,

you are correct that the current problem is, that your trigger acts on
all rows of the table instead of just the inserted.

In the trigger context, all inserted rows are available in the virtual
table "inserted". (Lookup "triggers, inserted tables" in BOL for more
information).

Assuming "id" is the primary key of your table, you could use:

CREATE TRIGGER TR_TFACP200
ON TFACP200
FOR INSERT
AS

UPDATE TFACP200
SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)
WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1
AND DOCUMENT_DATE IS NULL
AND EXISTS (
SELECT 1
FROM inserted
WHERE inserted.id = TFACP200.id
)

Hope this helps,
Gert-Jan
Milind wrote:

I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind

I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.

Originally posted by Dtb
I am having trouble creating an INSTEAD OF trigger in SQL Server to

replicate a BEFORE UPDATE trigger from ORACLE.

Here is a sample of the ORACLE BEFORE UPDATE trigger:

CREATE TRIGGER myTRIGGER ON MYTABLE

begin

:new.DT := SYSDATE;

if :new.NM is NULL then

:new.NM := USER;

end if;

end myTRIGGER;


It seems as though I have to jump through hoops in SQL Server AND I

cannot come up with correct results.


Here is a snippet from SQL SERVER (this is what I figured I needed to

do after reading various articles,questions):

CREATE TRIGGER myTRIGGER on THETABLE

INSTEAD OF UPDATE

AS


SELECT * INTO #MYTABLE FROM INSERTED


UPDATE #MYTABLE SET DT = GETDATE()

UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL


UPDATE THETABLE

SET

DT = (SELECT DT FROM #MYTABLE),

NM = (SELECT NM FROM #MYTABLE)

WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)



Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: A. Barnet | last post by:
Hi, all, I posted a similar message at comp.soft-sys.sas. I used code like the following via SAS PROC SQL pass-thru to create a table named user.tmp in Oracle: drop table user.tmp; create...
38
by: Mike | last post by:
No flame wars, please! We're planning a move from a non-relational system to a relational system. Our choices have been narrowed to Oracle and DB2. Since we're moving from non-relational to...
7
by: KingGreg | last post by:
All, Oracle 9i provides a "USING" clause option for inner joins, that allows me to say: SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn assuming KeyColumn is in both TBL1 and TBL2. This is...
8
by: Tavish Muldoon | last post by:
At a high level - what would be involved in switching from Oracle to DB2? Pretty vague, I know - but anyone have experience with this kind of migration? Pointers? Things to look for? Tmuld
1
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this...
6
by: Jeff Kish | last post by:
Hi. I'm a casual sql user. I have found a situation where I need to convert an oracle statement to tsql, one I can just fire off in any sql tool against an ms sql server database. I studied the...
3
by: Fred Morrison | last post by:
Does DB2 have anything equivalent to Oracle's PRAGMA AUTONOMOUS_TRANSACTION that allows a stored procedure to BEGIN, COMMIT or ROLLBACK a totally independent transaction while possibly (but not...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
1
by: Shawn Hirn | last post by:
In article <3EF75784.9040004@netscape.net>, Frank <fvanbortel@netscape.netwrote: I am stumped. I am a very very new Oracle user. A colleague put the Oracle 10g client on my workstation...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.