can u ignore errors in a trigger

if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the tableI don't think so.

What kind of errors are you getting?|||you might be able to eat them in 2005 with try/catch. however it seems better to eliminate the root cause of the error, rather than covering it up.|||DROP TRIGGER <trigger_name>

But I would fix the trigger|||I suppose you could temorarily disable triggers. That seems like a dumb thing to do, but you could... I guess.

Can Triggers Help Me out

Dear Experts,
I am storing the rates in pricelist table and these rates are used in
several tables.
Is is possible to create a trigger that will change rates in all the tables
wherever i have used the moment someone changes the rates in pricelist?
Thanks
Manish Sawjiani
Three Cheers to Technet for the Help!
Hi Manish
Triggers can help you, but u need to write
UPDATE <TABLE> ...
...
...
and so on, for all the tables that involved the price...
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Manish Sawjiani" wrote:

> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the tables
> wherever i have used the moment someone changes the rates in pricelist?
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Manish Sawjiani wrote:
> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the
> tables wherever i have used the moment someone changes the rates in
> pricelist?
> Thanks
> Manish Sawjiani
Why not create a relationship between the rates and these other tables
if they need to be kept in sync with the main table? If that won't work,
you can use an update trigger and the inserted table to get a list of
all rows that were updated.
David Gugick
Imceda Software
www.imceda.com

Can Triggers Help Me out

Dear Experts,
I am storing the rates in pricelist table and these rates are used in
several tables.
Is is possible to create a trigger that will change rates in all the tables
wherever i have used the moment someone changes the rates in pricelist?
Thanks
Manish Sawjiani
--
Three Cheers to Technet for the Help!Hi Manish
Triggers can help you, but u need to write
UPDATE <TABLE> ...
..
..
and so on, for all the tables that involved the price...
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Manish Sawjiani" wrote:
> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the tables
> wherever i have used the moment someone changes the rates in pricelist?
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!|||Manish Sawjiani wrote:
> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the
> tables wherever i have used the moment someone changes the rates in
> pricelist?
> Thanks
> Manish Sawjiani
Why not create a relationship between the rates and these other tables
if they need to be kept in sync with the main table? If that won't work,
you can use an update trigger and the inserted table to get a list of
all rows that were updated.
--
David Gugick
Imceda Software
www.imceda.comsql

Can trigger firing/execution be viewed in Profiler?

If so, how? I've looked through all the event classes and don't see
anything about triggers.
Also, I added the DatabaseName column to my trace but all the entries
have this column set to NULL. Is there something I need to do to get
this column populated correctly?
Thanks!If you add SP:StmtStarting or SP:StmtCompleted events, you will see trigger
execution.
See if you can get Database ID column populated. This way you can resolve
the name using DB_NAME() function.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Franklin Bowen" <FranklinBowen@.hotmail.com> wrote in message
news:9142752b.0309100902.6e873a1a@.posting.google.com...
If so, how? I've looked through all the event classes and don't see
anything about triggers.
Also, I added the DatabaseName column to my trace but all the entries
have this column set to NULL. Is there something I need to do to get
this column populated correctly?
Thanks!

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

can someone tell me what I am doing wrong

HI all,
I have this trigger on a table
**********
CREATE TRIGGER addtotal ON dbo.ClaimFinancialLoss
AFTER INSERT, UPDATE, DELETE
AS
if update(Ammount)
begin
Declare @.tot int, @.id int
select @.id = ClaimID from inserted
--Print 'Id after insert' +str(@.id)
if exists (select * from deleted)
Begin
select @.id = ClaimID from deleted
--Print 'Id after deleted' +str(@.id)
end
Select @.tot=sum(Ammount) from ClaimFinancialLoss group by ClaimID Having
ClaimID = @.id
update claim set total = @.tot where ClaimID = @.id
end
**************
This updates the claim table. On the claim table I have these two triggers
(they are kept seperate just for simplicity at the moment)
****************
CREATE TRIGGER dateupdated ON dbo.Claim
AFTER INSERT
AS
declare @.id int
select @.id=claimid from inserted
update claim set updated = getdate() where claimid = @.id
*************
CREATE TRIGGER layerchange ON [dbo].[Claim]
FOR INSERT, UPDATE AS
if update(layerid)
select layerid as ins from inserted
select layerid as del from deleted
declare @.id int
select @.id=claimid from inserted
begin
delete from ClaimDeductables where claimid = @.id
insert into claimdeductables SELECT PolLayer.LayerCap,
PolLayer.PayOrder,claim.claimid, Layer.LayerID
FROM Claim INNER JOIN
CP_Covertype ON Claim.LayerID = CP_Covertype.CPCKey
INNER JOIN
Covertype ON CP_Covertype.CTKey = Covertype.CTKey
INNER JOIN
PolLayer ON Covertype.CTKey = PolLayer.CTKey INNER
JOIN
Layer ON PolLayer.layerid = Layer.LayerID
WHERE (Claim.ClaimID = @.id)
raiserror('You have made changes to the layers of this claim',9,1,1)
end
*****************
THe raiserror always fires, when ever I update Claimfinancialloss.ammount.
To me this error should not be raised As I have a if update(Layerid), and I
am not updating the layerid of the claim, only the total (through the
trigger.)
Is having two update triggers on the same table, one of them updating the
claimtable again, causing the layerid column to simulate a be updated.
Thanks
Robertyou need a begin after if update(layerid) because if without begin
looks only at the first statement after the if
example
declare @.x int
select @.x =4
if @.x <> 4
print 'yes'
print'blah'
you see, blah will alway be printed
declare @.x int
select @.x =4
if @.x <> 4
begin
print 'yes'
print'blah'
end
If you use begin and end this won't happen since the if will skip that
whole statement
http://sqlservercode.blogspot.com/|||Without investigating this further, you missed that a trigger is fired
per STATEMENT NOT per ROW, so you better should investigate eliminating
this misdesign.
HTH, jens Suessmeyer.|||HI,
YEp, you hit the nail on the head.
Stupid me, I did have a begin statement after the iff statement, But then
during testing I inserted two select statements after the if statement,
naturally the first select statement was executed as a direct result of the
if statment then the other statments executed as a matter of course.
Thanks
Robert
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140101176.000869.264260@.g43g2000cwa.googlegroups.com...
> you need a begin after if update(layerid) because if without begin
> looks only at the first statement after the if
> example
> declare @.x int
> select @.x =4
> if @.x <> 4
> print 'yes'
> print'blah'
> you see, blah will alway be printed
>
> declare @.x int
> select @.x =4
> if @.x <> 4
> begin
> print 'yes'
> print'blah'
> end
> If you use begin and end this won't happen since the if will skip that
> whole statement
> http://sqlservercode.blogspot.com/
>|||HI Jens,
Yes I realise that, as I said this is a tempory measure, testing. But
anyway, I was pointed to the fact that I never had my begin statement in the
right place by another post.
Thanks anyway for you valued input
Robert
<Jens.Suessmeyer@.googlemail.com> wrote in message
news:1140101824.576386.320190@.g14g2000cwa.googlegroups.com...
> Without investigating this further, you missed that a trigger is fired
> per STATEMENT NOT per ROW, so you better should investigate eliminating
> this misdesign.
> HTH, jens Suessmeyer.
>

can not send an email using databasemail from asp.net page in a trigger

Hello,

I want to send an email from a trigger. I have configured all, and if I insert something into the table directly in the db the email is sent.

I give access to the asp.net account to msdb and put it into DatabaseMailUserRole group, but it doesnt work when I launch the trigger.

Any ideas?

What is the error you are getting?

Did you try inserting the row from Query window to check db mail working?

Check mail log for more info...

|||that is what i do not understand. It do not give me any error. If I insert from query window it works, but from asp.net fails. I think it is something related to the asp.net account, but i am using atlas and i can not see the error message. Nothing to see in the log, except a transacction exception, but I do not see that always, sometimes it writes it sometimes not.