Trigger RRS feed

  • Question

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











    trigger mano on all server







    INSERT INTO mano..DDL_Role



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



    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


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:



    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.


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


    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.


    • 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



    EVENT NOTIFICATION event_notification_name_3



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



    INSERT INTO manoj..DDL_Role



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



    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.

    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.


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