邪恶八进制信息安全团队技术讨论组 » 常用数据库类{ Database Security } » 名门之后[ MSSQL ] » [转载]sql server 2005中的DDL触发器 [查看完整版本]

ring04h 2006-6-27 17:57

[转载]sql server 2005中的DDL触发器

<p>信息来源: 邪恶八进制信息安全团队</p><p>SQL SERVER 2005中，新增加了许多新的特性，其中的DDL触发器是个不错的选择，根据资料初步学习如下，现整理之： </p><p /><p /><div id="3"><div id="19"></div>在sql server 2000中，只能为针对表发出的 DML 语句（INSERT、UPDATE 和 DELETE）定义 AFTER 触发器。SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如，CREATE_TABLE）或者为一组语句（例如，DDL_DATABASE_LEVEL_EVENTS）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。 </div><p /><p /><div id="4"><div id="19"></div>比如，在SQL SERVER 2005中，建立一个叫DDLTrTest 的数据库，并且建立一个叫mytable的表 <br /><div id="19"></div>和Usp_Querymytable 的存储过程，如下所示 </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DATABASE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">DDLTRTEST</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DATABASE</span><span style="COLOR: #000000"> DDLTRTEST
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">USE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">DDLTRTEST</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    IFEXISTS (</span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> SYS.OBJECTS
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">OBJECT_ID</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">OBJECT_ID</span><span style="COLOR: #000000">(N</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">[DBO].[MYTABLE]</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #808080">AND</span><span style="COLOR: #000000"> TYPE </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"> (N</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">U</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">))
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">DBO</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">.</span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">MYTABLE</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> MYTABLE(ID </span><span style="FONT-WEIGHT: bold; COLOR: #000000">INT</span><span style="COLOR: #000000">, NAME </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">100</span><span style="COLOR: #000000">))
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">A</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">B</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">C</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">4</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">D</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">5</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">E</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">6</span><span style="COLOR: #000000">,</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">F</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">USE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">DDLTrTest</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    IFEXISTS (</span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> sys.objects </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">object_id</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #ff00ff">OBJECT_ID</span><span style="COLOR: #000000">(N</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">[dbo].[usp_querymytable]</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #808080">AND</span><span style="COLOR: #000000"> type </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> (N</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">P</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">, N</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">PC</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">))
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">PROCEDURE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">dbo</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">.</span><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">usp_querymytable</span><span style="COLOR: #ff0000">]</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">PROC</span><span style="COLOR: #000000"> USP_QUERYMYTABLE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">AS</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> MYTABLE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">GO</span><span style="COLOR: #000000"></span></div>
</div></pre></div><p /><div id="6"><div id="19"></div>接下来定义一个DDL触发器如下 <div id="19"></div></div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="7">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TRIGGER</span><span style="COLOR: #000000"> STOP_DDL_on_Table_and_PROC
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ON</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DATABASE</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"> CREATE_TABLE,DROP_TABLE,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ALTER_TABLE,CREATE_PROCEDURE,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    ALTER_PROCEDURE,DROP_PROCEDURE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">AS</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> EVENTDATA().value
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    (</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">nvarchar(max)</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">PRINT</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">You are not allowed to CREATE,ALTER and DROP
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    any Tables and Procedures</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ROLLBACK</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="8"><div id="19"></div>接下来，我们尝试如下的操作： </div><p /><p /><div id="9"><div id="19"></div>ALTER TABLE MYTABLE ADD X INT </div><p /><p /><div id="10"><div id="19"></div>结果如下，出现错误提示 </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="11">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">ALTER</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> MYTABLE </span><span style="COLOR: #0000ff">ADD</span><span style="COLOR: #000000"> X </span><span style="FONT-WEIGHT: bold; COLOR: #000000">INT</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    (</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> row(s) affected)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    You are </span><span style="COLOR: #808080">not</span><span style="COLOR: #000000"> allowed </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000">,</span><span style="COLOR: #0000ff">ALTER</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">any</span><span style="COLOR: #000000"> Tables </span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> Procedures
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    Msg </span><span style="FONT-WEIGHT: bold; COLOR: #800000">3609</span><span style="COLOR: #000000">, </span><span style="COLOR: #0000ff">Level</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000">, State </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000">, Line </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    The </span><span style="COLOR: #0000ff">transaction</span><span style="COLOR: #000000"> ended </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> the </span><span style="COLOR: #0000ff">trigger</span><span style="COLOR: #000000">. The batch has been aborted. </span></div>
</div></pre></div><p /><div id="12"><div id="19"></div>再执行DROP的操作，同样触发警告 </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="13">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> MYTABLE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    (</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> row(s) affected)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    You are </span><span style="COLOR: #808080">not</span><span style="COLOR: #000000"> allowed </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000">,</span><span style="COLOR: #0000ff">ALTER</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">DROP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">any</span><span style="COLOR: #000000"> Tables </span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> Procedures
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    Msg </span><span style="FONT-WEIGHT: bold; COLOR: #800000">3609</span><span style="COLOR: #000000">, </span><span style="COLOR: #0000ff">Level</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000">, State </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000">, Line </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    The </span><span style="COLOR: #0000ff">transaction</span><span style="COLOR: #000000"> ended </span><span style="COLOR: #808080">in</span><span style="COLOR: #000000"> the </span><span style="COLOR: #0000ff">trigger</span><span style="COLOR: #000000">. The batch has been aborted</span></div>
</div></pre></div><p /><div id="14"><div id="19"></div>因为我们的触发器规定了不能使用CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_PROCEDURE、ALTER_PROCEDURE、DROP_PROCEDURE等操作。</div><p /><p /><div id="15"><div id="19"></div>如果我们要关掉这个触发器，可以这样做： </div><p /><p /><div id="16"><div id="19"></div>DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE</div><p /><p /><div id="17"><div id="19"></div>当然，我们要对整个服务器采取策略的话，也是很简单的，和上面的方法大致相同只不过将on database的参数改为on server,比如</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="18">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CREATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TRIGGER</span><span style="COLOR: #000000"> STOP_DDL_on_Table_and_PROC
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ON</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">ALL</span><span style="COLOR: #000000"> SERVER
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"> CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">AS</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">PRINT</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">You are not allowed to CREATE,ALTER and DROP any Databases</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />    </span><span style="COLOR: #0000ff">ROLLBACK</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div>

查看完整版本: [转载]sql server 2005中的DDL触发器

© 1999-2008 EvilOctal Security Team