SQL Server 2000 Trigger

forum.connectivity (1998-2005) & forum.td.connectivity (2005-2010)
peftraun
Austria
Posts: 56
Joined: 17 Nov 2018, 09:58
Location: Austria

SQL Server 2000 Trigger

Post by peftraun » 11 Oct 2006, 15:31

 Posted by:  Fritz Peinbauer 

Hello NG,

I am migrating application from Oracle to SqlServer 2000 and would need some
assistence:

I need a trigger (update , insert), which manipulates data of
inserted/updated row.
for example on update the column 'modnr' should increment by 1 (indicating
row data change to other users)

create table t1 (id int, name varchar(100), modnr int )

update name='newname' where id=1 should result in incremented modnr

in Oracle I would creat this trigger:
CREATE OR REPLACE TRIGGER formelpos_MODNR

BEFORE INSERT OR UPDATE ON FORMELPOS

FOR EACH ROW

BEGIN

IF INSERTING THEN

:NEW.Modnr := 0;

END IF;

IF UPDATING THEN

:NEW.Modnr := :OLD.Modnr + 1;

END IF;

END;

What would a similar trigger solution look like in SqlServer2000?

thank you very much
Fritz

Fridolin Schwarz

SQL Server 2000 Trigger

Post by Fridolin Schwarz » 11 Oct 2006, 18:00

 Posted by:  Fridolin Schwarz 


Hi Fritz,

as far as I know, there is nothing like a FOR EACH ROW-Trigger in SQL
Server.
But you can define your modnr-column as timestamp, so it changes value with
every UPDATE.

fritz

peftraun
Austria
Posts: 56
Joined: 17 Nov 2018, 09:58
Location: Austria

SQL Server 2000 Trigger

Post by peftraun » 11 Oct 2006, 19:16

 Posted by:  Fritz Peinbauer 

Hi Fridolin,

I found a solution which seems working:

CREATE TRIGGER formelpos_MODNR ON dbo.FORMELPOS
AFTER UPDATE
AS
BEGIN
UPDATE dbo.FORMELPOS
SET Modnr = Modnr + 1
WHERE id IN (SELECT i.id
FROM inserted i);
END

I do not want to change to timestamp because I want to reuse exiting
application locig (modnr)

Thank you for your answer!
Fritz

Rick Cogan

SQL Server 2000 Trigger

Post by Rick Cogan » 13 Oct 2006, 23:32

 Posted by:  Rick Cogan 

The CONNECT BY construct is an Oracle extension only. You will need to do it
by brute force in Sql Server!

rc

Return to “td.connectivity”

Who is online

Users browsing this forum: [Ccbot] and 0 guests