Re: How do I create a trigger in a stored procedure?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 29 Aug 2009 09:44:11 +0000 (UTC)
Mark B (none123@xxxxxxxx) writes:
Thanks very much.
DISABLE TRIGGER LanguageValuesTrigger ON DATABASE
UPDATE tblLanguageValues
SET LanguageText = REPLACE(LanguageText,'"', '"')
WHERE (LanguageCode <> 'EN-US')
UPDATE tblLanguageValues
SET LanguageText = REPLACE(LanguageText,''', char(39))
WHERE (LanguageCode <> 'EN-US');
ENABLE TRIGGER LanguageValuesTrigger ON DATABASE
And this works? Since you say ON DATABASE, that should refer to a
DDL trigger on database level, not a trigger on a table. But maybe
SQL Server let's go away with it.
In any case, you should absolutely have BEGIN/COMMIT TRANSACTION
around this, so that you don't leave the trigger disabled in case of
an accident. That also prevents other users from accessing the table
while the transaction is running. (Or at least I hope it does; I did
not test.)
By the way, do I need to use the word GO after the DISABLE and ENABLE
commands? Seems to compile OK without it. What is the purpose of GO
anyway? And I needed to use the ; before ENABLE to compile. What's the
difference between that and GO and when do I need to use it?
GO and ; are two very different things. In fact, when it comes to SQL
Server alone, "GO" is just another identifier like Foo, Bar or
LanguageText. It's not a command. "GO" is something that is intercepted
by many query tools. When you press F5, SSMS takes the selected area
(or the entire window) and sends it to SQL Server, but not in one piece.
For each GO it finds on a line of its own, it stops and sends what it
has so far to SQL Server. Thus, GO is a batch separator.
This means that if you put a "GO" in the middle of your stored procedure
code, this is the same effect as if you delete all code below this
point, at least with regards to the procedure definition. (The rest of
the code will be executed directly.)
; on the other hand is a statement terminator, just like in many other
languages. The only difference is that in Transact-SQL, the semicolon
is optional in most cases. This is because originally T-SQL did have
semicolons at all. There are a few situations when you need it, and
that is when a statement begins with a keyword that is not "statement
leader".
Microsoft's recommendation is that you should always terminate your
statements with semicolons, and they say that one day, they will be
mandatory in SQL Server. Personally, I can't see how they will ever
be able to this without causing misery and mayhem all over the planet,
and I will have to confess that I don't put in more semicolons in my
SQL code that I actually need to.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: How do I create a trigger in a stored procedure?
- From: Mark B
- Re: How do I create a trigger in a stored procedure?
- References:
- How do I create a trigger in a stored procedure?
- From: Mark B
- Re: How do I create a trigger in a stored procedure?
- From: Tom Cooper
- Re: How do I create a trigger in a stored procedure?
- From: Mark B
- How do I create a trigger in a stored procedure?
- Prev by Date: Re: NEWSEQUENTIALID() behaviour while adding a default to the existing table
- Next by Date: Re: Server trigger - how do i detect if exists using TSQL?
- Previous by thread: Re: How do I create a trigger in a stored procedure?
- Next by thread: Re: How do I create a trigger in a stored procedure?
- Index(es):
Relevant Pages
|
Loading
