MSDE (MSSqlSrv 7.0) and trigger

Hello,

i got a msde database (core based on ms sql server 7.0) and want to create a trigger.

Unfortunetaly, msde doesn't know before trigger.
As far as i know, i can access the new values in an update trigger by selecting it from the 'inserted' table. Is there any possibility to access the values in the database before the update action happens ? or..
How can i write a trigger that checks a state column and admit updates only to state = 8 if the old state value was 6 ?

thanks in advance.

dirk

example.. it doesn't work :(

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN
declare @.state int
set @.state=(select Status from inserted)
declare @.notation int
set @.notation = (select Bezeichnung from inserted)
if (@.state = 8 AND (select Status from Auftrag where Bezeichnung=@.notation)!=6)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end
end;Originally posted by SeattleDirk
Hello,

i got a msde database (core based on ms sql server 7.0) and want to create a trigger.

Unfortunetaly, msde doesn't know before trigger.
As far as i know, i can access the new values in an update trigger by selecting it from the 'inserted' table. Is there any possibility to access the values in the database before the update action happens ? or..
How can i write a trigger that checks a state column and admit updates only to state = 8 if the old state value was 6 ?

thanks in advance.

dirk

example.. it doesn't work :(

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN
declare @.state int
set @.state=(select Status from inserted)
declare @.notation int
set @.notation = (select Bezeichnung from inserted)
if (@.state = 8 AND (select Status from Auftrag where Bezeichnung=@.notation)!=6)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end
end;

I hope you have id or primary key in your table(let say id is unique).
It needs to remember that you can update more than 1 record(may be not from your application - but it is better assume this anyway).
I did not test this - but it has to work.

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN

if exists(select 1 from deleted d
join inserted i on i.id=d.id
where d.status=6 and i.status<>8)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end