How to make a Trigger for more tan 1 table?

Hi,
I have to put a Trigger on +-100 tables, and the Trigger will perform the
same action. Is there away to Create jsut 1 trigger that will work for all
the tables?
This for:
- don't having to make 100 times the same trigger
- just having to do maintenance on only 1 trigger and not 100
Any help would be really aprpeciated,
Thansk a lot,
PieterWhat about sending the action inside the trigger to a common stored
procedure which could be maintained easily ?
(Therefore is must be coded very good to keep up SQL Server db health ;-) )
HTH, Jens Suessmeyer.
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:escuv1WSFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>|||Hi
No, a trigger can only be bound to a single table.
A Trigger can call a stored procedure, but the Inserted and Deleted tables
are not available to the SP. You could put all your logic in the SP. But you
still have to put 100 triggers in place.
What does your trigger do?
Regards
Mike
"DraguVaso" wrote:
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>
>|||Thanks.
My Triggrer has to insert the SQL-Query that fired the trigger in another
table, and add if it was an update/insert/delete trigger.
So I guess I won't be able to put it in a seperate SP...
this is my trigger:
CREATE TRIGGER TriggerName
ON categories
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
@.QueryType as nvarchar(10)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
IF EXISTS (SELECT * FROM inserted)
BEGIN
SET @.QueryType = 'INSERT'
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'UPDATE'
END
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'DELETE'
END
END
INSERT INTO tblTransactions
SELECT @.Qry AS Query,
SYSTEM_USER as LoginName,
USER AS UserName,
CURRENT_TIMESTAMP AS QueryDate,
@.QueryType AS QueryType
END
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> Hi
> No, a trigger can only be bound to a single table.
> A Trigger can call a stored procedure, but the Inserted and Deleted tables
> are not available to the SP. You could put all your logic in the SP. But
you
> still have to put 100 triggers in place.
> What does your trigger do?
> Regards
> Mike
> "DraguVaso" wrote:
> > Hi,
> >
> > I have to put a Trigger on +-100 tables, and the Trigger will perform
the
> > same action. Is there away to Create jsut 1 trigger that will work for
all
> > the tables?
> > This for:
> > - don't having to make 100 times the same trigger
> > - just having to do maintenance on only 1 trigger and not 100
> >
> > Any help would be really aprpeciated,
> >
> > Thansk a lot,
> >
> > Pieter
> >
> >
> >|||Did you consider third party tools for this, tools that are using the transaction log and probably
have less overhead? I've listed some of those tools on my links page (the log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> My Triggrer has to insert the SQL-Query that fired the trigger in another
> table, and add if it was an update/insert/delete trigger.
> So I guess I won't be able to put it in a seperate SP...
> this is my trigger:
> CREATE TRIGGER TriggerName
> ON categories
> FOR INSERT, UPDATE, DELETE AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> @.QueryType as nvarchar(10)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> INSERT INTO #inputbuffer
> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> IF EXISTS (SELECT * FROM inserted)
> BEGIN
> SET @.QueryType = 'INSERT'
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'UPDATE'
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'DELETE'
> END
> END
> INSERT INTO tblTransactions
> SELECT @.Qry AS Query,
> SYSTEM_USER as LoginName,
> USER AS UserName,
> CURRENT_TIMESTAMP AS QueryDate,
> @.QueryType AS QueryType
> END
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> Hi
>> No, a trigger can only be bound to a single table.
>> A Trigger can call a stored procedure, but the Inserted and Deleted tables
>> are not available to the SP. You could put all your logic in the SP. But
> you
>> still have to put 100 triggers in place.
>> What does your trigger do?
>> Regards
>> Mike
>> "DraguVaso" wrote:
>> > Hi,
>> >
>> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> the
>> > same action. Is there away to Create jsut 1 trigger that will work for
> all
>> > the tables?
>> > This for:
>> > - don't having to make 100 times the same trigger
>> > - just having to do maintenance on only 1 trigger and not 100
>> >
>> > Any help would be really aprpeciated,
>> >
>> > Thansk a lot,
>> >
>> > Pieter
>> >
>> >
>> >
>|||I did consider them, but they aren't free... :-(
do you know some opensource/free tools?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Did you consider third party tools for this, tools that are using the
transaction log and probably
> have less overhead? I've listed some of those tools on my links page (the
log reader tools).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> > Thanks.
> > My Triggrer has to insert the SQL-Query that fired the trigger in
another
> > table, and add if it was an update/insert/delete trigger.
> >
> > So I guess I won't be able to put it in a seperate SP...
> >
> > this is my trigger:
> >
> > CREATE TRIGGER TriggerName
> > ON categories
> > FOR INSERT, UPDATE, DELETE AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> > @.QueryType as nvarchar(10)
> >
> > CREATE TABLE #inputbuffer
> > (
> > EventType nvarchar(30),
> > Parameters int,
> > EventInfo nvarchar(255)
> > )
> >
> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >
> > INSERT INTO #inputbuffer
> > EXEC (@.ExecStr)
> >
> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >
> > IF EXISTS (SELECT * FROM inserted)
> > BEGIN
> > SET @.QueryType = 'INSERT'
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'UPDATE'
> > END
> > END
> > ELSE
> > BEGIN
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'DELETE'
> > END
> > END
> >
> > INSERT INTO tblTransactions
> > SELECT @.Qry AS Query,
> > SYSTEM_USER as LoginName,
> > USER AS UserName,
> > CURRENT_TIMESTAMP AS QueryDate,
> > @.QueryType AS QueryType
> > END
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> Hi
> >>
> >> No, a trigger can only be bound to a single table.
> >>
> >> A Trigger can call a stored procedure, but the Inserted and Deleted
tables
> >> are not available to the SP. You could put all your logic in the SP.
But
> > you
> >> still have to put 100 triggers in place.
> >>
> >> What does your trigger do?
> >>
> >> Regards
> >> Mike
> >>
> >> "DraguVaso" wrote:
> >>
> >> > Hi,
> >> >
> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> > the
> >> > same action. Is there away to Create jsut 1 trigger that will work
for
> > all
> >> > the tables?
> >> > This for:
> >> > - don't having to make 100 times the same trigger
> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >
> >> > Any help would be really aprpeciated,
> >> >
> >> > Thansk a lot,
> >> >
> >> > Pieter
> >> >
> >> >
> >> >
> >
> >
>|||> do you know some opensource/free tools?
I don't think that there are any free log reader tools out there. I recall some free tool that
generates triggers for you, but I didn't find it now, looking though my links. Might be worth
googling for, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
>I did consider them, but they aren't free... :-(
> do you know some opensource/free tools?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Did you consider third party tools for this, tools that are using the
> transaction log and probably
>> have less overhead? I've listed some of those tools on my links page (the
> log reader tools).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> > Thanks.
>> > My Triggrer has to insert the SQL-Query that fired the trigger in
> another
>> > table, and add if it was an update/insert/delete trigger.
>> >
>> > So I guess I won't be able to put it in a seperate SP...
>> >
>> > this is my trigger:
>> >
>> > CREATE TRIGGER TriggerName
>> > ON categories
>> > FOR INSERT, UPDATE, DELETE AS
>> > BEGIN
>> > SET NOCOUNT ON
>> >
>> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
>> > @.QueryType as nvarchar(10)
>> >
>> > CREATE TABLE #inputbuffer
>> > (
>> > EventType nvarchar(30),
>> > Parameters int,
>> > EventInfo nvarchar(255)
>> > )
>> >
>> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
>> >
>> > INSERT INTO #inputbuffer
>> > EXEC (@.ExecStr)
>> >
>> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
>> >
>> > IF EXISTS (SELECT * FROM inserted)
>> > BEGIN
>> > SET @.QueryType = 'INSERT'
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'UPDATE'
>> > END
>> > END
>> > ELSE
>> > BEGIN
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'DELETE'
>> > END
>> > END
>> >
>> > INSERT INTO tblTransactions
>> > SELECT @.Qry AS Query,
>> > SYSTEM_USER as LoginName,
>> > USER AS UserName,
>> > CURRENT_TIMESTAMP AS QueryDate,
>> > @.QueryType AS QueryType
>> > END
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> >> Hi
>> >>
>> >> No, a trigger can only be bound to a single table.
>> >>
>> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> tables
>> >> are not available to the SP. You could put all your logic in the SP.
> But
>> > you
>> >> still have to put 100 triggers in place.
>> >>
>> >> What does your trigger do?
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "DraguVaso" wrote:
>> >>
>> >> > Hi,
>> >> >
>> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
>> > the
>> >> > same action. Is there away to Create jsut 1 trigger that will work
> for
>> > all
>> >> > the tables?
>> >> > This for:
>> >> > - don't having to make 100 times the same trigger
>> >> > - just having to do maintenance on only 1 trigger and not 100
>> >> >
>> >> > Any help would be really aprpeciated,
>> >> >
>> >> > Thansk a lot,
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >> >
>> >
>> >
>>
>|||ok thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OP9wqrXSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > do you know some opensource/free tools?
> I don't think that there are any free log reader tools out there. I recall
some free tool that
> generates triggers for you, but I didn't find it now, looking though my
links. Might be worth
> googling for, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
> >I did consider them, but they aren't free... :-(
> > do you know some opensource/free tools?
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> >> Did you consider third party tools for this, tools that are using the
> > transaction log and probably
> >> have less overhead? I've listed some of those tools on my links page
(the
> > log reader tools).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> >> > Thanks.
> >> > My Triggrer has to insert the SQL-Query that fired the trigger in
> > another
> >> > table, and add if it was an update/insert/delete trigger.
> >> >
> >> > So I guess I won't be able to put it in a seperate SP...
> >> >
> >> > this is my trigger:
> >> >
> >> > CREATE TRIGGER TriggerName
> >> > ON categories
> >> > FOR INSERT, UPDATE, DELETE AS
> >> > BEGIN
> >> > SET NOCOUNT ON
> >> >
> >> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> >> > @.QueryType as nvarchar(10)
> >> >
> >> > CREATE TABLE #inputbuffer
> >> > (
> >> > EventType nvarchar(30),
> >> > Parameters int,
> >> > EventInfo nvarchar(255)
> >> > )
> >> >
> >> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >> >
> >> > INSERT INTO #inputbuffer
> >> > EXEC (@.ExecStr)
> >> >
> >> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >> >
> >> > IF EXISTS (SELECT * FROM inserted)
> >> > BEGIN
> >> > SET @.QueryType = 'INSERT'
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'UPDATE'
> >> > END
> >> > END
> >> > ELSE
> >> > BEGIN
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'DELETE'
> >> > END
> >> > END
> >> >
> >> > INSERT INTO tblTransactions
> >> > SELECT @.Qry AS Query,
> >> > SYSTEM_USER as LoginName,
> >> > USER AS UserName,
> >> > CURRENT_TIMESTAMP AS QueryDate,
> >> > @.QueryType AS QueryType
> >> > END
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> >> Hi
> >> >>
> >> >> No, a trigger can only be bound to a single table.
> >> >>
> >> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> > tables
> >> >> are not available to the SP. You could put all your logic in the SP.
> > But
> >> > you
> >> >> still have to put 100 triggers in place.
> >> >>
> >> >> What does your trigger do?
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "DraguVaso" wrote:
> >> >>
> >> >> > Hi,
> >> >> >
> >> >> > I have to put a Trigger on +-100 tables, and the Trigger will
perform
> >> > the
> >> >> > same action. Is there away to Create jsut 1 trigger that will work
> > for
> >> > all
> >> >> > the tables?
> >> >> > This for:
> >> >> > - don't having to make 100 times the same trigger
> >> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >> >
> >> >> > Any help would be really aprpeciated,
> >> >> >
> >> >> > Thansk a lot,
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Open source tools are not free. Like any other software (including
anything you develop) they have a cost of implementation and ownership.
Sorry to state the obvious but the fallacy that you implied is too
commonly repeated. The cost of acquiring software is typically only a
small fraction of the cost of owning and running it.
If you want to log SQL statements without any capital outlay then you
could use SQL Profiler. You pay for this in performance on your server
though, so you may well find the third-party tools are cheaper :-).
--
David Portas
SQL Server MVP
--