Check if there is trigger

Hi,

How can I check if there is trigger for one table, and if yes disable?

if ( there is trigger in cm_template = yes )

ALTERTABLE cm_template DISABLETRIGGER TRG_cm_template

else

do stuff...

Since I try disable one trigger in a table which there is not trigger I think I'll receive one error. am I right?

cheers,

If you are using 2005, this is really easy:

select object_name(parent_id) as table_name, name as trigger_name, is_disabled
from sys.triggers

For 2000, you can use:

SELECT OBJECT_NAME (parent_obj), name,
OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') as is_disabled
FROM sysobjects
WHERE type = 'TR'

Credit: By Srinivas Sampath MCSE, MVP (SQL Server) in his article: http://sqljunkies.com/Article/F5783CE9-BA74-4657-BF9D-1139BCC3096B.scuk

sql

Check if Delete ran in a trigger

I am new to SQL Server and I am trying to write a trigger where I am doing a
delete on another table. I need to know whether this delete fails or not.
How can I achieve this?
TIA
Altmancheck the rowcount in the trigger
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||What do you mean by fail? If the delete fails with an error message, like a
foreign key constraint
violation, the trigger will not be fired. If you mean modify zero rows, chec
k @.@.ROWCOUNT the very
first thing you do in the triggers, or check the number of rows in the delet
ed table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.
gbl...
>I am new to SQL Server and I am trying to write a trigger where I am doing
a delete on another
>table. I need to know whether this delete fails or not. How can I achieve
this?
> --
> TIA
> Altman
>
>|||I mean that I have a trigger and inside of that trigger I am calling a
Delete from table ..... I need to know if the delete that I am calling
inside the trigger goes through.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> What do you mean by fail? If the delete fails with an error message, like
> a foreign key constraint violation, the trigger will not be fired. If you
> mean modify zero rows, check @.@.ROWCOUNT the very first thing you do in the
> triggers, or check the number of rows in the deleted table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Altman" <NotGiven@.SickOfSpam.com> wrote in message
> news:ODx2Uhl2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||It depends on what you mean by "goes though". If it generates and error, you
can catch that error in
your trigger code (@.@.error), just like you do in any TSQL code. And you can
get the number of rows
modified using @.@.ROWCOUNT. I suggest you check out the error handling articles at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Altman" <NotGiven@.SickOfSpam.com> wrote in message news:O4jWsSm2FHA.2624@.TK2MSFTNGP09.phx.
gbl...
>I mean that I have a trigger and inside of that trigger I am calling a Dele
te from table ..... I
>need to know if the delete that I am calling inside the trigger goes throug
h.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJ008Jm2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

Check for Null values in Transact Sql

I am using Visual Web Developer Express 2005 and SQL Server Express 2005.

I have set up a trigger that fires when an update has been performed on
a specific table. The trigger checks to see if specific columns have
been updated. If they have, then the trigger code is executed. This part
works fine.

I am now trying to find a way to check if null values exist in either
one of two field from the same table. If either field contains a null
value, then I don't want the trigger code to be executed.

How can I check for null values and skip a block of code within my
Transact Sql trigger.

Thanks....

You can use the IS NULL clause to test a column...

|||

Use something like:

if exists (select * from MyTbl where Col1 is null or Col2 is null)

//do not update

else //update

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.
The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check Database Operation

Hi,
I want to check that which operation like Insert, Update or Delete is
performed on the table in a trigger written for all the these opertaions.
Is there is any function from which I can know this?
Thanks.The only way you can tell in a trigger is the presence or absence of rows in
the inserted and deleted table:
1. No rows in either table - nothing was changed
2. Rows in inserted , none in deleted - Insert
3. Rows in deleted , non in inserted - Delete
4. Rows in both - Update
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:uzu3YIgHEHA.548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to check that which operation like Insert, Update or Delete is
> performed on the table in a trigger written for all the these opertaions.
> Is there is any function from which I can know this?
> Thanks.
>

Check Constraint ot Instead of trigger ?

Hi All.. I have a vendor supplied application that writes to a very large
table. I have identified a set of data that the vendors client software send
s
the database that we do not use, but adds 1 million rows of data each day.
I've added an "After" trigger to delete this data as it gets inserted, but
I'm searching for a way from keeping it from getting inserted in the first
place. I can create a check constraint or an instead of trigger. The docs
say use contraints when you can, but does anyone have a different opinion as
how to redirect data to the bit bucket and not insert it into the table ?
And, no the vendor won't help and I can't say who it is ? :>Is the Vendor insert coming from a Strored Procedure? If so, implement your
logic inside the stored procedure before even doing anything to the table
itself.
"JohnW" wrote:

> Hi All.. I have a vendor supplied application that writes to a very large
> table. I have identified a set of data that the vendors client software se
nds
> the database that we do not use, but adds 1 million rows of data each day.
> I've added an "After" trigger to delete this data as it gets inserted, but
> I'm searching for a way from keeping it from getting inserted in the first
> place. I can create a check constraint or an instead of trigger. The docs
> say use contraints when you can, but does anyone have a different opinion
as
> how to redirect data to the bit bucket and not insert it into the table ?
> And, no the vendor won't help and I can't say who it is ? :>|||On Wed, 26 Apr 2006 14:09:02 -0700, JohnW wrote:

>Hi All.. I have a vendor supplied application that writes to a very large
>table. I have identified a set of data that the vendors client software sen
ds
>the database that we do not use, but adds 1 million rows of data each day.
>I've added an "After" trigger to delete this data as it gets inserted, but
>I'm searching for a way from keeping it from getting inserted in the first
>place. I can create a check constraint or an instead of trigger. The docs
>say use contraints when you can, but does anyone have a different opinion a
s
>how to redirect data to the bit bucket and not insert it into the table ?
>And, no the vendor won't help and I can't say who it is ? :>
Hi John,
Adding a CHECK constraint will prevent the insertion of the data and
throw an error. Also, if 100 rows are inserted in a single INSERT
statement and 1 fails the CHECK constraint, they are all rejected. If
this is the behaviour you need, AND the thrid party application can
handle the error condition raised by the CHECK contraint, use it.
Otherwise, use an INSTEAD OF trigger. That can prevent insertion of all
rows, or of only the offending rows, without sputtering error messages
to the client.
Hugo Kornelis, SQL Server MVP|||Just another solution, though if u can do it in the SP thats the best way..
You can use a for insert trigger and
check if the insert should happen, if not rollback the transaction.
like this
(this not syntax.. just pseudo code) check this anyways
create trigger t1 "for insert" on tbl1
begin
if condition = false
rollback tran
end