创建触发动态

发布于 2024-07-21 05:35:08 字数 347 浏览 8 评论 0原文

我使用 MS SQL 2008，我想在动态创建的数据库中创建一个触发器。

创建数据库是在其他数据库的存储过程中调用的，并且运行完美，但是当我想添加触发器或存储过程时，执行失败。

如果我尝试使用以下

EXEC('USE dbase
GO
CREATE TRIGGER [blah]
GO')

命令运行动态 SQL：

“GO”附近的语法错误

如果我删除“USE ...”，触发器将在错误的数据库中创建。

有什么技巧可以避免我的问题吗？

谢谢

原文

I use MS SQL 2008 and I want to create a trigger in a database that is created dynamic.

Creating the database is called within a stored procedure of an other database and runs perfectly, but when I want to add a trigger or a stored procedure, the executing fails.

If I try to run the dynamiy SQL with an

EXEC('USE dbase
GO
CREATE TRIGGER [blah]
GO')

I get:

Wrong syntax near 'GO'

And if I remove the 'USE ...' the trigger will be created in the wrong database.

Is there a trick to avoid my problems?

Thx

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぇ气 2024-07-28 05:35:08

“GO”不是 T-SQL 语言。它是由 SSMS 等客户端工具解释为批次分隔符的关键字（意味着“将文本发送到服务器”）。

现在，CREATE TRIGGER 必须是批处理中的第一个语句，因此不能使用“USE dbname”。

如果您在 EXEC 之前提到“USE dbnname”，那么它可能会出现在连接的默认数据库中。您必须进行测试（抱歉，我现在不能）

--Might work
USE dbase
EXEC ('CREATE TRIGGER [blah]
')

或者您必须使用 sqlcmd 或 osql 来连接并运行代码：这允许您在连接上设置数据库上下文。但不在 T-SQL 内。

或者您可以在 EXEC 之前尝试 ALTER LOGIN xxx WITh DEFAULT_DATABASE = dbname

ALTER LOGIN xxx WITH DEFAULT_DATABASE = dbname 
--Now the EXEC will connect to default db if above approach fails
EXEC('CREATE TRIGGER [blah]
')

"GO" is not T-SQL language. It's a keyword interpreted by client tools like SSMS as a batch separator (that means "send text to server").

Now, CREATE TRIGGER must be the first statement in the batch so the "USE dbname" can not used.

If you mention "USE dbnname" before the EXEC, then it may up in the default database for the connection. You'd have to test (I can't right now, sorry)

--Might work
USE dbase
EXEC ('CREATE TRIGGER [blah]
')

Or you'll have to use sqlcmd or osql to connect and run the code: this allows you to set the db context on connection. Not within T-SQL though.

Or you can try ALTER LOGIN xxx WITh DEFAULT_DATABASE = dbname before EXEC

ALTER LOGIN xxx WITH DEFAULT_DATABASE = dbname 
--Now the EXEC will connect to default db if above approach fails
EXEC('CREATE TRIGGER [blah]
')

回复收藏 0 原文

深白境迁sunset 2024-07-28 05:35:08

您可以在调用 exec 之前切换数据库，然后立即切换回来：

use newdb
exec ('CREATE TRIGGER [blah] ...')
use originaldb

或者创建一个指向正确数据库的链接服务器，并启用 RPC，并且：

EXEC ('CREATE TRIGGER [blah] ...') AT LinkedServerName

或者创建一个在您想要的数据库中具有默认目录的其他用户创建触发器并：

EXECUTE AS LOGIN = 'UserName'
EXEC ('CREATE TRIGGER [blah] ...')
REVERT

You can switch the database before calling exec, and switch back right after:

use newdb
exec ('CREATE TRIGGER [blah] ...')
use originaldb

Or create a linked server to the right database, with RPC enalbed, and:

EXEC ('CREATE TRIGGER [blah] ...') AT LinkedServerName

Or create a different user that has the Default Catalog in the database where you'd like to create the trigger and:

EXECUTE AS LOGIN = 'UserName'
EXEC ('CREATE TRIGGER [blah] ...')
REVERT

回复收藏 0 原文

路还长，别太狂 2024-07-28 05:35:08

无需“更改登录..”，使用这种方式进行动态数据库和动态触发器

set @db = 'XXX'
set @sql = 'use [' + @db + ']; 

exec (''CREATE TRIGGER [dbo].[Trigger1] ON dbo.Table1
    FOR UPDATE, INSERT, DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        /*your code*/
    END;'')'

exec (@sql)

No need to "Alter Login .." use this way for dynamic db and dynamic trigger

set @db = 'XXX'
set @sql = 'use [' + @db + ']; 

exec (''CREATE TRIGGER [dbo].[Trigger1] ON dbo.Table1
    FOR UPDATE, INSERT, DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        /*your code*/
    END;'')'

exec (@sql)

回复收藏 0 原文

~没有更多了~