Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Any Column Updated / Inserted Trigger

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.>Hi,
>I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
>this is possible. I'm running SQL Server 2000 on Win2k Server
>I have one table with a large number of columns. I have two pieces of
>logic that I'd like to execute depending upon whether an insert or an
>update statement was executed on that table. I'd prefer this
>execution to occur from within a single trigger. If a row is
>inserted, then I would like to execute logic A. If ANY column in the
>table is updated, then I'd like logic B to be executed.
>Is it possible to just determine if only "insert" or an "update"
>ocurred from within the a single Trigger, without specifying each
>individual column name? (I.E. not saying IF udpate(col1) or
>update(col2) or ect...) Is it possible to just perform a check on the
>process that occurred, irregardless of column? Like If INSERTED =
>TRUE then execute insert logic. If UPDATED = TRUE, then run the
>updated logic. I would like for all of this code to be stored within
>the same trigger.
>If anyone can provide some sample code on how to do this, if at all
>possible, I would be much appreciative.
>Thanks,
>-Rigs
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @.ChgInsert CHAR(1)
DECLARE @.ChgDelete CHAR(1)
DECLARE @.ChgCode CHAR(1)
SET @.ChgInsert = 'N'
SET @.ChgDelete = 'N'
SET @.ChgCode = 'N'

IF exists(select top 1 FROM inserted)
SET @.ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @.ChgDelete = 'Y'

/* Check for a insert */
IF @.ChgInsert = 'Y' AND @.ChgDelete = 'N'
Begin
SET @.ChgCode = 'I'
End

/* Check for a change */
IF @.ChgInsert = 'Y'AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'C'
End

/* Check for a delete */
IF @.ChgInsert = 'N' AND @.ChgDelete = 'Y'
Begin
SET @.ChgCode = 'D'
End

IF @.ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @.ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @.ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END

Randy
http://members.aol.com/rsmeiner|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo|||On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
> >PS I know I could do this with 2 seperate triggers, but I'm trying to
> >avoid that.
> Hi Rigs,
> Why are you trying to avoid using 2 seperate triggers?
> I see a case for combining insert and update triggers if use need to
> execute the SAME code on insert and on update. But since you have to
> execute different code for each case, I'd think that using two
> triggers provides a better documented system that's easier to
> understand and easier to maintain. And your performance will improve
> as well (allthough so little that you won't notice).
> Best, Hugo

Anti Virus Software 0n Cluster

Hi,
We are building a new Win2k Adv, SQL 2000 cluster.
what is the best way to install the anti virus software after the cluster
has been built....do we need to pause the the node, and then install anti
virus software etc...
Cheers Ray
Guess that depends on the software, though they all seem to act the same to
me. Trend, Norton, etc. can be installed live, from the management console
of the product. NO reboot require. Very simple process.
I think the question you need to ask is, what directory(s) should I exclude
from being scanned.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
<Ray> wrote in message news:OPEfPga7EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are building a new Win2k Adv, SQL 2000 cluster.
> what is the best way to install the anti virus software after the cluster
> has been built....do we need to pause the the node, and then install anti
> virus software etc...
> Cheers Ray
>
|||Make sure that the antivirus program's filters excludes the
o\MSCS directory on the quorum
oall SQL data (mdf, ndf), log (ldf), Errorlog, Fulltext directories
o\MSDTC directory used by MS DTC
See KBs
http://support.microsoft.com/?kbid=250355
http://support.microsoft.com/?kbid=309422
for additional details.
HTH,
Wish you a Very Happy and Properous New Year.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Do not install anti-virus on a cluster host node EVER!
Antivirus Software May Cause Problems with Cluster Services
http://support.microsoft.com/default...b;en-us;250355
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<Ray> wrote in message news:OPEfPga7EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are building a new Win2k Adv, SQL 2000 cluster.
> what is the best way to install the anti virus software after the cluster
> has been built....do we need to pause the the node, and then install anti
> virus software etc...
> Cheers Ray
>
|||I have not had a problem with TrendMicro ServerProtect 5.58. Exclude lots of
stuff, but it works great ;)
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OcEHjyc7EHA.2124@.TK2MSFTNGP14.phx.gbl...
> Do not install anti-virus on a cluster host node EVER!
> Antivirus Software May Cause Problems with Cluster Services
> http://support.microsoft.com/default...b;en-us;250355
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <Ray> wrote in message news:OPEfPga7EHA.1404@.TK2MSFTNGP11.phx.gbl...
>
|||Only problem I have actually experienced with anti-virus and SQL is with
tlog backups. We were storing emails in a database and the AV caught a
virus signature inside a transaction log backup file. BAD THINGS then
happened.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:eXKBd%23c7EHA.3820@.TK2MSFTNGP11.phx.gbl...
> I have not had a problem with TrendMicro ServerProtect 5.58. Exclude lots
of[vbcol=seagreen]
> stuff, but it works great ;)
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcEHjyc7EHA.2124@.TK2MSFTNGP14.phx.gbl...
cluster
>
|||I've read that article. But the corporate IT security often has more say in
whether to install the anti-virus software, though we can control what
files/directories to exclude from the scan. Often, that means all computers
must have anti-virus software installed and up to date. SQL boxes--clustered
or not--are no exception. It would be really bad for a DBA to wake up one
day finding that the SQL box is infected with a SQL Slammer like virus
because he told the management not to install any anti-virus software there.
Fortunately, we've not had any problem with running anti-virus on cluster
nodes so far.
Linchi
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OcEHjyc7EHA.2124@.TK2MSFTNGP14.phx.gbl...
> Do not install anti-virus on a cluster host node EVER!
> Antivirus Software May Cause Problems with Cluster Services
> http://support.microsoft.com/default...b;en-us;250355
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <Ray> wrote in message news:OPEfPga7EHA.1404@.TK2MSFTNGP11.phx.gbl...
>
|||"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:OQBprUj7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> I've read that article. But the corporate IT security often has more say
in
> whether to install the anti-virus software, though we can control what
> files/directories to exclude from the scan. Often, that means all
computers
> must have anti-virus software installed and up to date. SQL
boxes--clustered
> or not--are no exception. It would be really bad for a DBA to wake up one
> day finding that the SQL box is infected with a SQL Slammer like virus
> because he told the management not to install any anti-virus software
there.
Not in my opinion.
It would be really bad for the DBA to wake up one day finding that the SQL
box is infected with a SQL Slammer like virus because he didn't apply the
patches that had been available for months previous.
Note that AV software is almost always purely reactionary. It generally
needs a virus signature, which means the virus has to have been in the wild
previously.
In addition, I'm not sure any AV software would catch a virus like Slammer
that's memory resident and runs basically within the scope of an existing
application. (technically it was a worm in any case.)
So, in case you can't tell, I'm not a huge fan of AV software on SQL
Servers. :-
[vbcol=seagreen]
> Fortunately, we've not had any problem with running anti-virus on cluster
> nodes so far.
> Linchi
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcEHjyc7EHA.2124@.TK2MSFTNGP14.phx.gbl...
cluster
>
|||I have 'ownership' of the entire SQL boxes for our web site operations. I
also have significant influence on the internal operations group. No AV on
clusters and massive exclusions on SQL Servers is the rule around here. Of
course, all our servers are in heavily defended subnets that have to go
through firewalls to get to the outside world. Our network guys are pretty
good at protecting our systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:OQBprUj7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> I've read that article. But the corporate IT security often has more say
in
> whether to install the anti-virus software, though we can control what
> files/directories to exclude from the scan. Often, that means all
computers
> must have anti-virus software installed and up to date. SQL
boxes--clustered
> or not--are no exception. It would be really bad for a DBA to wake up one
> day finding that the SQL box is infected with a SQL Slammer like virus
> because he told the management not to install any anti-virus software
there.[vbcol=seagreen]
> Fortunately, we've not had any problem with running anti-virus on cluster
> nodes so far.
> Linchi
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcEHjyc7EHA.2124@.TK2MSFTNGP14.phx.gbl...
cluster
>