Before create a procedure send a trigger to a table

Question

Before create a procedure send a trigger to a table

ulisseslourenco

SSC-Addicted

Points: 417
More actions
August 9, 2011 at 11:02 am

#255478

Hello folks
I´d like to know if I can create a trigger that verifies procedure name exists into a catalog table, this case I´d like to force an user insert a catalog table before create a procedure.
best regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can use DDL triggers to fire on creation of a procedure. They can check values in tables. Will that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 2

GSquared (8/9/2011)
You can use DDL triggers to fire on creation of a procedure. They can check values in tables. Will that do what you need?

In fact I would like to search for procedure name into a table before. If i create a trigger create before I wouldn´t like to send any kind of create object.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ulisseslourenco (8/9/2011)
GSquared (8/9/2011)
You can use DDL triggers to fire on creation of a procedure. They can check values in tables. Will that do what you need?
In fact I would like to search for procedure name into a table before. If i create a trigger create before I wouldn´t like to send any kind of create object.

so something during the scripting process? and not a trigger at all, really?

IF EXISTS (SELECT 1 FROM SomeTable where value='NewPProcName')

BEGIN

EXEC 'CREATE PROCEDURE ....'

END

ELSE

BEGIN

PRINT 'You must have added the table before we add the procedure?'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 4

Lowell (8/9/2011)
ulisseslourenco (8/9/2011)
GSquared (8/9/2011)
You can use DDL triggers to fire on creation of a procedure. They can check values in tables. Will that do what you need?
In fact I would like to search for procedure name into a table before. If i create a trigger create before I wouldn´t like to send any kind of create object.
so something during the scripting process? and not a trigger at all, really?
IF EXISTS (SELECT 1 FROM SomeTable where value='NewPProcName')
BEGIN
EXEC 'CREATE PROCEDURE ....'
END
ELSE
BEGIN
PRINT 'You must have added the table before we add the procedure?'
END

However We don´t create a procedure from a script file, blocking in Management Studio will be better.So I thought about a trigger instead of script.