SQL Server Performance Forum – Threads Archive

Triggers in SQL Server

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

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

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.

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.

.. 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=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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.

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.

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

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.

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.

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

]]>