How to make argument/parameter optional in UDF/Trigger?

Hello,
I took over a project, and I need to make updates, modifications, pick up
where the last person left off. There is one table that is used in several
processess from external apps. This table contains an InsteadOf trigger tha
t
I need to disable for just one new procedure that am adding to the project.
I am thinking of using a UDF where I pass a value to the UDF and that value
disables the trigger. But the other processes/procedures which are already
in place obviously do not have a call to this UDF, and I sure don't want to
spend the time hunting down every process/procedure that uses this table (al
l
the external apps). Or alternatively, is there a way to create an optional
parameter in the trigger - where if a value is passed in, the trigger gets
disabled, else trigger does its thing. The other processes/procedures woul
d
not pass in this optional parameter. Is there a way to do this?
Thanks,
RichI think I am getting discombobulated. I don't think you can pass things in
to Triggers. Can you?
I am thinking I could set a value in a support table that only my procedure
would know about and then remove that value from the table when the process
is complete.
"Rich" wrote:

> Hello,
> I took over a project, and I need to make updates, modifications, pick up
> where the last person left off. There is one table that is used in severa
l
> processess from external apps. This table contains an InsteadOf trigger t
hat
> I need to disable for just one new procedure that am adding to the projec
t.
> I am thinking of using a UDF where I pass a value to the UDF and that val
ue
> disables the trigger. But the other processes/procedures which are alread
y
> in place obviously do not have a call to this UDF, and I sure don't want t
o
> spend the time hunting down every process/procedure that uses this table (
all
> the external apps). Or alternatively, is there a way to create an option
al
> parameter in the trigger - where if a value is passed in, the trigger gets
> disabled, else trigger does its thing. The other processes/procedures wo
uld
> not pass in this optional parameter. Is there a way to do this?
> Thanks,
> Rich

How to make a trigger refer to the current record

I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()

Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated... for example:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
where shipto_id = @.THIS_ID

I imagine there's some kind of builtin variable or something like that. How is this done?

Thanks in advance.CREATE TRIGGER tr_cp_shiptos_u ON dbo.cp_shiptos
FOR UPDATE
AS
BEGIN
UPDATE c
SET date_updated = GETDATE()
FROM cp_shiptos c
JOIN inserted i
ON i.key = c.key
END
GO

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,
Pieter
What 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[vbcol=seagreen]
> still have to put 100 triggers in place.
> What does your trigger do?
> Regards
> Mike
> "DraguVaso" wrote:
the[vbcol=seagreen]
all[vbcol=seagreen]
|||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...
> you
> the
> all
>
|||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).[vbcol=seagreen]
> --
> 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...
another[vbcol=seagreen]
tables[vbcol=seagreen]
But[vbcol=seagreen]
for
>
|||> 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...
> transaction log and probably
> log reader tools).
> another
> tables
> But
> for
>
|||ok thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OP9wqrXSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> 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[vbcol=seagreen]
> 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...
in[vbcol=seagreen]
(the[vbcol=seagreen]
perform
>
|||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

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

how to locate a table that contains a specific trigger?

Hello,
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:

> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>

How to Launch an Oracle SP from SQL Trigger?

I need to know a few things about this..
1. What do I need running or available on SQL Server machine (presuming an Oracle client)
2. Has anyone done this and perhaps can give me some quidance?
3. Any concerns about going from outside a firewall to the Oracle instance?

Architecture is probably going look like this:

SQL -> ON INSERT Trigger -> business logic -> Call Oracle Procedure

Any help is appreciated, thanks!

JTGISDo NOT try to create this Frankenstein monster of database architecture.
The scope of triggers should be limited to their own table if possible, and to their own database at the most. Otherwise, you risk your database stability and data integrity on a chain of technologies stretching across multiple database engines and right through your network.
This is NOT a good idea, and trying to do this sort of thing is what has given triggers a bad name over the years.
I encourage you, instead, to use a trigger on your table to populate a staging table, and then running a frequent batch process (scheduler can run once per minute) to process the data in the staging table and make whatever external calls to the Oracle database are necessary.
The big advantage of this setup is that if you network connection fails or your Oracle database goes down, your SQL Server database is unaffected and continues running merrily along loading data into the staging table, which can then be processed once all systems are up and working again with no loss of data and no loss of service on your SQL Server database.|||Blindman, thanks. I appreciate what you said and it makes sense. Ok, so no trigger other than to populate and use a storage table with the salient records.

What mechanisms exist to push this data into an Oracle table? That is the really important part because I do not want Oracle to have to pull that data. I know Oracle has transparent gateways and access managers for manipulating other data sources. Where do I go to make the same sorts of things happen for SQL server? My biggest constraint is that the data must be no older than a few seconds for this application is analyzing utility data for electrical outages. Optimally I could populate the salient records to an oracle resident table and the oracle side can handle itself (I already have that side built but I am having to "punt" due to problems with an SNMP tool failure that indicates the need for a better, more straight forward architecture).

Any pointers would be appreciated.|||To send the data to Oracle, use either a DTS package or the OPENQUERY function.

how to know if trigger is disabled?

Is there a functoin or property that you can look at to tell if a trigger
has been disabled? Something in the schema?
Like:
Print IsTriggerDisabled(tblCustomers.MyTrigger)
KeithHi
For more information please read this article and that will answer your
question:
http://sqljunkies.com/Article/F5783...39BCC3096B.scuk
thanks and regards
Chandra
"Keith G Hicks" wrote:

> Is there a functoin or property that you can look at to tell if a trigger
> has been disabled? Something in the schema?
> Like:
> Print IsTriggerDisabled(tblCustomers.MyTrigger)
> Keith
>
>|||Cool. Thank you. :)
- keith

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