ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

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.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...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DTB" <macdtb@.mac.com> wrote in message
news:4af8df45.0308170552.27c24433@.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.|||Thank you Dan! This works the way I want it to.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<ZfN%a.27352$vo2.5843@.newsread1.news.atl.earthlink. 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...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
> "DTB" <macdtb@.mac.com> wrote in message
> news:4af8df45.0308170552.27c24433@.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.|||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|||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