SQL Server Performance

Triggers in SQL Server

Discussion in 'Getting Started' started by Asif Ali, May 10, 2007.

  1. Asif Ali New Member

    Please tell me atleast six T-SQL statements which are not usable in triggers.
  2. thomas New Member

    you could simply look up CREATE TRIGGER in Books Online like I did.

    Is this an interview question?


    quote:
    These Transact-SQL statements are not allowed in a trigger:

    ALTER DATABASE
    CREATE DATABASE
    DISK INIT
    DISK RESIZE
    DROP DATABASE
    LOAD DATABASE
    LOAD LOG
    RECONFIGURE
    RESTORE DATABASE
    RESTORE LOG

  3. Adriaan New Member

    If you include any of the statements listed by thomas, then the CREATE TRIGGER or ALTER TRIGGER statement will fail.

    The one that is really taboo in triggers, so much so that BOL probably doesn't even mention it, is PRINT. But it doesn't make the CREATE TRIGGER or ALTER TRIGGER statement fail, so there you go.

    You would use PRINT in triggers for debugging from Query Analyzer, but many client applications (Access for instance) go funny when they get something thrown at them from a trigger. So clean up after yourself when the debugging is done.
  4. Adriaan New Member

    And another taboo is to call xp_cmdshell.

    Triggers hold up the transaction until its full code finishes executing, so if the xp_cmdshell command is taking a lot of time, the whole server may grind to a halt.
  5. satya Moderator

    .. there is much more info available as per Thomas's suggestion -<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/aa258254>http://msdn2.microsoft.com/en-us/library/aa258254</a>(SQL.80).aspx if you are wondering what is books online [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  6. Asif Ali New Member

    quote:Originally posted by thomas

    you could simply look up CREATE TRIGGER in Books Online like I did.

    Is this an interview question?


    quote:
    These Transact-SQL statements are not allowed in a trigger:

    ALTER DATABASE
    CREATE DATABASE
    DISK INIT
    DISK RESIZE
    DROP DATABASE
    LOAD DATABASE
    LOAD LOG
    RECONFIGURE
    RESTORE DATABASE
    RESTORE LOG

    I am very thankful.
    yes this is an interview question. one more i have to ask what is the stored procedure through which we can call COM object.
  7. MohammedU New Member

    There are some sp_OAXXX extended stored procedure which can be used to call COM objects...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. FrankKalis Moderator

    That's an interesting interview question. Wonder, what they expect from this question?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  9. satya Moderator

    IMHO the originator should dig more into the references provided above in order to know more about it. If it is a genuine SQL problem them you are most welcome.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. Asif Ali New Member

    quote:Originally posted by MohammedU

    There are some sp_OAXXX extended stored procedure which can be used to call COM objects...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Do you receive any message that row size is ended and no more records entered?
    When you update any row in enterprise manager in physical table, is there any option to roll back as auto commit is on in SQL server or you physically select the row and update information?
    Please solve these issues.
  11. FrankKalis Moderator

    Are these questions related to your trigger question? If not, it is better to start a new thread with a more to your question related subject. That will attract more people to read your question and help you.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

Share This Page