August 27, 2008 at 10:52 pm
Hi everybody!
I'm trying to create the stored procedure CrTr on database 'a'. This stored procedure must create a trigger on the table 'b' on the database 'b'.
My script looks like this:
use [a]
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[CrTr]
AS
BEGIN
SET NOCOUNT ON;
declare @cmd1 nvarchar(1000)
set @cmd1 = '
use
go
CREATE TRIGGER Tr
ON b.dbo.b
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into a.dbo.a select * from inserted
END'
print @cmd1
exec sp_executesql @cmd1
This stored procedure is created without any error. But when I execute it I get the below error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 3
'CREATE TRIGGER' must be the first statement in a query batch.
I do know that the CREATE TRIGGER must be the first statement in a query batch. That's why I add
'USE b'
Go
to my code.
I eagerly would be appreciated if anybody help.
Thnx
Safa
August 27, 2008 at 11:05 pm
a well designed system does not create database objects on the fly like this.
You can't do what you are trying to do. If someone comes along and says "sure you can, just dynamic sql!" then don't listen.
🙂
---------------------------------------
elsasoft.org
August 27, 2008 at 11:41 pm
Allright 🙂
thank you for cooperation
Surely I would not listen to him as you said,
But kindly just tell me whether there is any way to make a trigger by stored procedure of another database?
Thank You in advance
August 28, 2008 at 9:52 am
in software, pretty much anything is possible. The point is that this is not a good idea.
why are you trying to do this? what's the backstory?
---------------------------------------
elsasoft.org
September 2, 2008 at 12:28 am
Alright,
Actually this script is a small part of a big project.
It is all about auditing. I'm trying to design a auditing mechanism that monitors the DML on any table of any database. I have a central Repository database. Procedure CrTr (or whatever) places in this database. Let's say I want to monitor the Address table of AdventureWorks database. I would give the AdventureWorks to my stored procedure as a parameter, the stored procedure creates the Audit_Address table on my Repository database according to the columns of Address table plus some other columns (host name, appname, and etc). Now I want the stored procedure create a trigger on Address table that sends the Insert, Delete and Update values to Audit_Address placed on Repository database. It is obvious that not all tables of all database need to be monitored. So, I cannot create the procedure on all databases. Just some tables have to be monitored on demand. On the other hand, the Audit_* tables must be located on a single database (here Repository). and the only way to capture the before and after values of a table is using triggers (or is there any?).
Well, this story is what made me to come up with that question. 😎
September 3, 2008 at 8:19 am
well, it looks like you got the answer you were looking for here:
http://www.sqlservercentral.com/Forums/Topic560115-324-1.aspx
you may also benefit from having a look at the triggers here:
http://www.nigelrivett.net/index.html#Triggers
---------------------------------------
elsasoft.org
September 3, 2008 at 9:39 am
September 3, 2008 at 9:49 am
Repeating the warning.... don't create triggers on the fly.
If you have a DBA job of creating audit triggers and want to write your own code here's how to bypass that problem.
Change the sp and drop the use/go part. Then put that sp in the db where you want to create the triggers, drop the sp when the job is done.
September 3, 2008 at 10:29 am
Actually I could solve the problem. However, I cannot understand why it is recommended to not create the triggers on the fly? My code now looks like this:
USE [MyProject]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[MkAdtTbl]
@FllTblName NVARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @sql1 NVARCHAR(1000)
DECLARE @sql2 NVARCHAR(1000)
DECLARE @sql3 NVARCHAR(1000)
DECLARE @sql4 NVARCHAR(1000)
DECLARE @sql5 NVARCHAR(2500)
DECLARE @TblName VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @AuditTblName VARCHAR(100)
DECLARE @tr VARCHAR(100)
SET @TblName = RTRIM(SUBSTRING(RTRIM(SUBSTRING(@FllTblName, CHARINDEX('.', @FllTblName)+1, 50)), CHARINDEX('.', RTRIM(SUBSTRING(@FllTblName, CHARINDEX('.', @FllTblName)+1, 50)))+1, 50))
SET @DbName = SUBSTRING(@FllTblName, 1, CHARINDEX('.', @FllTblName)-1)
SET @AuditTblName = 'Audit_' + @DbName + '_' + @TblName
SET @tr = 'Tr_' + @TblName
SET @sql1 = 'IF (SELECT OBJECT_ID("' + @AuditTblName + '","U")) IS NOT NULL DROP TABLE ' + @AuditTblName
SET @sql2 = 'SELECT TOP(0) * INTO ' + @AuditTblName + ' FROM ' + @FllTblName
EXEC sp_executesql @sql1
EXEC sp_executesql @sql2
SET @sql3 = 'ALTER TABLE ' + @AuditTblName + ' ADD
[audit_timestamp] [datetime] NOT NULL,
[audit_appname] [varchar](80) NULL,
[audit_terminal] [varchar](64) NULL,
[audit_login] [varchar](128) NULL,
[audit_user] [varchar](128) NULL,
[audit_statement] [varchar](6) NOT NULL,
[audit_value_type] [varchar](3) NOT NULL'
EXEC sp_executesql @sql3
SET @sql4 = 'USE ' + @DbName + '; EXEC ("IF OBJECT_ID('''+@Tr+''', ''Tr'') IS NOT NULL DROP TRIGGER '+@Tr+'")'
EXEC sp_executesql @sql4
SET @sql5 =
'USE ' + @DbName + ';
EXEC (
"CREATE TRIGGER ' + @tr + ' ON ' + @FllTblName +
'
FOR INSERT, UPDATE, DELETE WITH APPEND
AS
DECLARE @trans_type CHAR(6)
DECLARE @trans_time DATETIME
DECLARE @host VARCHAR(128)
DECLARE @app_name VARCHAR(128)
DECLARE @login_name VARCHAR(128)
DECLARE @table_name VARCHAR(12)
DECLARE @database_name VARCHAR(12)
IF EXISTS (SELECT 1 FROM inserted)
IF EXISTS (SELECT 1 FROM deleted)
SELECT @trans_type = ''UPDATE''
ELSE
SELECT @trans_type = ''INSERT''
ELSE
SELECT @trans_type = ''DELETE''
SELECT @trans_time = GETDATE(), @host=HOST_NAME(), @login_name = original_login(), @app_name = APP_NAME()
IF @trans_type = ''INSERT''
BEGIN
INSERT INTO MyProject.dbo.' + @AuditTblName +
' SELECT *, @trans_time, @app_name, @host, @login_name, USER, @trans_type, ''NEW'' FROM inserted
INSERT INTO MyProject.dbo.Uni VALUES (@trans_time, @app_name, @host, @login_name, USER, @trans_type, ''' + @TblName + ''', ''' + @DbName + ''')
END
ELSE IF @trans_type = ''DELETE''
BEGIN
INSERT INTO MyProject.dbo.' + @AuditTblName +
' SELECT *, @trans_time, @app_name, @host, @login_name, USER, @trans_type, ''OLD'' FROM deleted
INSERT INTO MyProject.dbo.Uni VALUES (@trans_time, @app_name, @host, @login_name, USER, @trans_type, ''' + @TblName + ''', ''' + @DbName + ''')
END
ELSE /* UPDATE */ BEGIN
INSERT INTO MyProject.dbo.' + @AuditTblName +
' SELECT *, @trans_time, @app_name, @host, @login_name, USER, @trans_type, ''OLD'' FROM deleted
INSERT INTO MyProject.dbo.' + @AuditTblName +
' SELECT *, @trans_time, @app_name, @host, @login_name, USER, @trans_type, ''NEW'' FROM inserted
INSERT INTO MyProject.dbo.Uni VALUES (@trans_time, @app_name, @host, @login_name, USER, @trans_type, ''' + @TblName + ''', ''' + @DbName + ''')
END
")'
EXEC sp_executesql @sql5
--Keeping Track
INSERT INTO MyProject.dbo.Meta VALUES (@DbName, @TblName, @AuditTblName, @tr, GETDATE())
And this works nicely.
September 3, 2008 at 10:42 am
The recommendation is to not create objects in production code.
You are in dev mode, which is mode than fine.
September 3, 2008 at 10:47 am
here's just a couple of things that I see wrong with your on-the-fly code:
1. SET "@TblName = (your code) would return null if a full part tablename wasn't passed to it (dbo.mytable vs myTable because it assumes a charindex for a period.
2. if proc is called and the audit table exists, you drop the table and recreate it, losing previously existing captured data.
3. it would be just as easy to script out ALL the triggers in one swoop, create them, and never need to do it again, except when new tables are added. either way, you have to "detect" when a table was added in order to create an audit trigger....I'd use a DDL trigger on the database to detect new tables, and use that as a reminder to create4 the audit tables, instead of using a proc, but that's just me.
4 until the proc is run, you don't have a trigger to create the audit...you din't mention it, but what determines when/if the proc is run? if you are just using it for a one time process as you whip thru a cursor of tables, that's the same as what I said in item 3 above...whether you use a programming language, a proc, or a macro to generate the triggers, it should be a one time up front process; I may have assumed you were doing it intermittantly...mia culpa.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply