Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: 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;

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: MSDE (MSSqlSrv 7.0) and trigger

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •