DDL Trigeer在SQL Server 2005中创建数据库时创建表 [英] DDL Trigeer In SQL Server 2005 to create tables when a database is created

问题描述

你好，

我想在sql server 2005中创建一个ddl触发器.当用户在sql server 2005中创建数据库时，它会在数据库中创建一些表.为此，我编写了一个代码，该代码在主表中创建了我创建的表.

谢谢

这是我用来创建触发器的代码.

Hello,

I want to create a ddl trigger in sql server 2005. which create some tables in database as user create a database in sql server 2005. For this i write a code, which create the tables in master table not in which i created.

Thanks

Here is my code which i use to create trigger.

Create TRIGGER [ddltrig_CreateDefaultTables]
ON ALL SERVER
FOR CREATE_DATABASE
AS
CREATE TABLE [dbo].[CategoryMaster](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CategoryMaster] PRIMARY KEY CLUSTERED 
(
[CategoryId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

推荐答案

我在这里假设您遇到的问题是在创建新数据库时触发触发器，但不是在新数据库的上下文中运行而是在master数据库下运行(因此尝试创建表)在师父)?

我必须做类似的事情，除了它是每个数据库的设置权限.为了完成它，我必须使用数据库名称来构建动态查询.遍历每个检查，查看数据库是否具有已创建的对象，如果没有，则创建它们.粗略地，我的任务是在所有数据库中检查和应用需要的地方.因此，我并不仅针对新数据库，还针对所有数据库.它运作良好，并确保权限始终正确.

I am assuming here the problem you are running into is that the trigger gets fired whe you create a new database but instead of running under the context of the new database it runs under the master database (thus trying to create your tables in Master)?

I had to do a similar thing, except it was setup permissions on each database. In order to acomplish it, I had to build dynamic queries with the database names. Loop through each check and see if the database had the obects already created if not then create them. Of coarse my task was to check and apply the perissions where needed against all databases. So I didnt target just the new database, but all databases. It works well and insures the persmissions are always correct.

Make sense?

罗希特，好消息.很高兴能够提供帮助.

Hi Rohit, that''s great news. Glad to have been able to help.

这篇关于DDL Trigeer在SQL Server 2005中创建数据库时创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！