Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

policies in sql server

Hi experts:

this trigger:
CREATE TRIGGER [ddl_trigger_create_proc]
ON DATABASE
AFTER CREATE_PROCEDURE
AS
DECLARE @name nvarchar(128)
SET @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
IF @name LIKE 'sp[_]%'
BEGIN
    RAISERROR ('You cannot create a stored procedure with a name starting with "sp_".  Re-create with a different name!', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO

This trigger can do with policies or directives of sql server? if so please outline the steps to do so.
0
enrique_aeo
Asked:
enrique_aeo
2 Solutions
 
ZberteocCommented:
This question is not very clear. What do you mean "trigger can do?".

On the other hand I think this is a bit too extreme. You don't need a trigger to prevent that naming convention. Even if you use it it i snot such a big deal. The only problem with "sp_" is that if you don't specify the database name when invoke the procedure the SQL engine will look for it in the master database first and if is not there it will look in your current database.

In some cases this is intentional and used by the best experts in SQL world, i.e., Brent Ozar with his sp_blitz, Adam Machanic with sp_whoisactive and Ola Hallegren with his maintenance solution. They created specialized procedures and solutions that help a lot with SQL administration and they all named their procedures with sp_ so that you can create them in the master database if you want. You don't have to, though, I prefered to create a dedicated DBA database where I created all these.

The advantage of creating an sp_ named procedure in master databse is that you can invoke it simply by name form any database you are in. If you create them in a dedicated database you either have to be in that database or to specify the database name when you invoke them from another one.
0
 
Eugene ZCommented:
please check this article with examples
DDL Triggers in SQL Server - audit database objects
Why do we need DDL triggers?
http://www.sqlbook.com/SQL-Server/DDL-Triggers-in-SQL-Server-34.aspx
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now