Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Trigger is fired according to user group

    Hello,

    I have create a update/delete/insert trigger for a table on the Server. the table is then connected with the front-End of Access to user A and user B group.

    Is is possible that I let the user A be able to fire the trigger after updating/deleting/inserting the content of the table while user B can NOT fire the trigger although the content of the table is also changed?

    Thanks a lot.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You would need to include code within the trigger to test for group membership and use that to determine which parts of the trigger should execute.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    cool, is there any example?

    How can I write the code to make such kind of compare? how to write it in TSQL?

    thank you!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE USER foo_demo WITHOUT LOGIN
    GO
    CREATE TABLE dbo.foo (
       fooId        INT	IDENTITY   NOT NULL
       CONSTRAINT XPKfoo PRIMARY KEY (fooId)
    ,  bar          DATETIME        NOT NULL
    ,  update_who   NVARCHAR(50)    NULL
    ,  update_when  DATETIME        NULL
    )
    GO
    --  ptp  20110718
    
    CREATE TRIGGER [dbo].[ti_foo]
    ON [dbo].[foo] 
    AFTER INSERT, UPDATE
    AS
       UPDATE [dbo].[foo]
          SET
             bar = CASE 
                WHEN 1 = IS_MEMBER('db_owner') THEN inserted.bar
                ELSE DATEADD(month, DateDiff(month, '2000-01-01', inserted.bar)
    ,              '1999-12-31')
             END
    ,        update_who = user_name()
    ,        update_when = GETDATE()
          FROM inserted
          WHERE  inserted.fooId = [dbo].[foo].[fooId]
    GO
    GRANT INSERT ON [dbo].[foo] TO foo_demo
    GO
    INSERT INTO [dbo].[foo] (bar) SELECT GETDATE()
    GO
    SETUSER 'foo_demo'
    GO
    INSERT INTO [dbo].[foo] (bar) SELECT GETDATE()
    GO
    SETUSER
    GO
    SELECT * FROM dbo.foo
    GO
    DROP USER foo_demo
    DROP TABLE [dbo].[foo]
    GO
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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