locked
Trigger RRS feed

  • Question

  • When i ran this peice of code in sql server 2005 instance,

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    create

     

    trigger mano on all server

    for

     

    ADD_SERVER_ROLE_MEMBER

    as

    BEGIN

     

    INSERT INTO mano..DDL_Role

    (

     

    xmlevent,login) VALUES (eventdata(),system_user)

    END

     

    I got error as,

    Msg 1082, Level 15, State 1, Procedure mano, Line 9

    "ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

     

    But the same code works in sql server 2008.

    What should i do to get this rectified???

    Wednesday, August 18, 2010 2:13 PM

Answers

All replies

  • Hi,

    You have used wrong event type. There is no such valied event type called ADD_SERVER_ROLE_MEMBER exists, try using CREATE_LOGIN event type.

    Hvae Look at more server scope event type here:

    http://msdn.microsoft.com/en-us/library/ms189871(SQL.90).aspx

     


    -Chintak
    Wednesday, August 18, 2010 2:34 PM
  • Hi Chintak,

    I didnt receive a response as invalid event type, i got as

    "ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

    Can you check the below link, its for sql server 2005. It clerly shows that, we have such event in 2005. See the child & parent details of this event.

    http://msdn.microsoft.com/en-us/library/ms179504(v=SQL.90).aspx

    Wednesday, August 18, 2010 2:54 PM
  • Yes, there is just an event, but Raul Garcia has answered this question at:

    http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/542bdafc-989d-4e83-84bf-08e7b3019d26/

    He said in this case:  "The cause for this error is that some events (such as adding members to a server role) cannot be used on asynchronous or non-transacted statements, and in such cases you need to use Event notifications."

    So, if you are just logging this, you can catch it through Event Notifications and insert from there into mano..DDL_Role.

    RLF

    • Proposed as answer by Tom Li - MSFT Friday, August 20, 2010 7:34 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 26, 2010 12:05 PM
    Wednesday, August 18, 2010 3:21 PM
  • Thanks a lot!!!! so can i get the syntax & procedure for implementing event notifications for add_role_member etc.

    I tried by creating it, like

    CREATE

     

    EVENT NOTIFICATION event_notification_name_3

    ON

     

    SERVER FOR drop_role_member to service 'broker_service' , 'manojS'

    begin

     

    INSERT INTO manoj..DDL_Role

    (

     

    xmlevent,databasename,systemuser) VALUES (eventdata(),db_name(),system_user)

    end

     

    but when i do a select on the table, im not getting the values of eventdata(), its displaying as null..

     

    Can you help me out to get this resolved.. Thanks in advance.

    Thursday, August 19, 2010 2:05 PM
  • Hi manoj.ks,

     

    “CREATE EVENT NOTIFICATION” doesn’t support “BEGIN END” syntax, it has special syntax. For more information about its syntax, you could refer to this link: http://msdn.microsoft.com/en-us/library/ms189453%28SQL.90%29.aspx

     

    Meanwhile if we want to use EVENT NOTIFICATION, there is much work we have to do, I recommend that you refer to this link to get more information: http://msdn.microsoft.com/en-us/library/ms182602%28v=SQL.90%29.aspx

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Friday, August 20, 2010 8:54 AM
  • Hi Tom,

     

    Thanks a lot. I am able to create event notification and when i tested, data is getting inserted into table specified for it, but xmlevent column alone is not getting updated. It is like

    xmlevent DatabaseName SystemUser

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------------------------------------------

    NULL Manoj id

    NULL Manoj id

    NULL Manoj id

    NULL Manoj id

    NULL Manoj id

    NULL Manoj id

    LEt me know how to get this eventdata() value into xmlevent column.

    Friday, August 20, 2010 2:11 PM
  • Hi manoj,ks

    Thanks for your post.

    Since the second issue in your latest post is more related to Service Broker, could you please ask it in SQL Service Broker? Then you will receive quicker reply.

     


    Regards,
    Tom Li
    Friday, August 20, 2010 2:57 PM