kill running query: action

Showing posts with label action. Show all posts

Friday, March 23, 2012

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
--

Sunday, February 19, 2012

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > Hi all.
> > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > fired it is an INSERT or an UPDATE?
> > Looking at 'deleted' virtual values perhaps?
> > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > TIA
> > Cheers.
> > Carlos.
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
> > Carlos,
> > Rows in inserted and deleted = UPDATE
> > Rows in inserted only = INSERT
> > Rows in deleted only = DELETE
> > BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> > because the columns have either explicit values or implicit (NULL) values
> > inserted.
> > RLF
> > "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> >news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > > Hi all.
> > > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > > fired it is an INSERT or an UPDATE?
> > > Looking at 'deleted' virtual values perhaps?
> > > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > > TIA
> > > Cheers.
> > > Carlos.
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
> >Hi all.
> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
> >If we have a INSERT, UPDATE trigger, how do we know if the action that
> >fired it is an INSERT or an UPDATE?
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegroups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
>> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>> >Hi all.
>> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
>> >If we have a INSERT, UPDATE trigger, how do we know if the action that
>> >fired it is an INSERT or an UPDATE?
>> Hi Carlos,
>> If the trigger has to do different things on insert and update, why not
>> create two triggers? Saves you the need to find out what the action was,
>> and might result in better performance to boot!
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
>
>
>
>
>
>
>
>
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:

>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>
>
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegroups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.
Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>
|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update
|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
>
>
>
>
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.
|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
>
>
>
>
>
>
>
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos
|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute
|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.
|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:

>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.
|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegr oups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

Friday, March 23, 2012

How to make a Trigger for more tan 1 table?

How to make a Trigger for more tan 1 table?

Sunday, February 19, 2012

How to know if INSERTing or UPDATing?

How to know if INSERTing or UPDATing?

How to know if INSERTing or UPDATing?

kill running query

Blog Archive

About Me