insert does not work when triggerd Stored Procedure fails

insert does not work when triggerd Stored Procedure fails

Post by Louis Davidso » Tue, 20 Jan 2004 01:40:02



Post the code and table dml.  Also, what errors are you getting.  Doesn't
sound too hopeful though.  Most any error that occurs while in a trigger
will cause the transaction to rollback and the batch to end.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.veryComputer.com/

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)


Quote:> I am trying to make a trigger on a table that will pass a value from the

insert to a stored procedure , the stored procedure then  would update a 2nd
table. I've noticed that if the stored procedure fails  the insert on the
origi*table fails as well. Is there any way around this?
 
 
 

insert does not work when triggerd Stored Procedure fails

Post by Erland Sommarsko » Tue, 20 Jan 2004 01:49:16



> I am trying to make a trigger on a table that will pass a value from the
> insert to a stored procedure , the stored procedure then  would update a
> 2nd table. I've noticed that if the stored procedure fails  the insert
> on the origi*table fails as well. Is there any way around this?

If you get an error in a trigger, the batch is aborted and the transaction

trigger.

But the story does not end there. a DML statement INSERT/DELETE/UPDATE)
is a transaction of its own, if you have not started an explicit
transaction. Thus, the statement should either be carried out in
full or not at all. And the trigger is part of that transaction. That
is, if the trigger does not succeed, neither should the parent
statement.

If the second update is not essential to you, you need to find some
other way. Since I don't know what purpose the other update fills, I
decline to give any suggestions.

--

Books Online for SQL Server SP3 at
http://www.veryComputer.com/

 
 
 

insert does not work when triggerd Stored Procedure fails

Post by Grant Cas » Tue, 20 Jan 2004 01:48:07


Leif,

A trigger is an implied transaction within SQL Server, so just like any
transaction an error will rollback the entire transaction.  That said, you
probably need to catch the error before it occurs in your stored procedure.
For instance, if inserting a record is failing the procedure because of a PK
violation you need to catch that before it occurs by testing for the
condition.  This will ensure that you can continue processing without
rolling back your entire transaction.  Either way, any time some error
occurs in your stored procedure and its called from the trigger; the entire
transaction including the original update, insert or delete will fail.  Your
only possible recourse is to catch the error in your procedure before it
happens.

Grant


Quote:> I am trying to make a trigger on a table that will pass a value from the

insert to a stored procedure , the stored procedure then  would update a 2nd
table. I've noticed that if the stored procedure fails  the insert on the
origi*table fails as well. Is there any way around this?
 
 
 

insert does not work when triggerd Stored Procedure fails

Post by Leif » Tue, 20 Jan 2004 02:26:22


Grant
Can you give me an example of how to catch, or test for the error( I assume in the stored procedure)?   Is there something like if error>0 else ??

I have two table
table 1
data1  char 24
data2  int

 table 2
data1a char  24

the trigger is as follows

create trigger Testdatatotable 2
on table1 for insert as

begin



end

go


as
begin
insert [table2](data1a)

end
go

 
 
 

insert does not work when triggerd Stored Procedure fails

Post by Grant Cas » Tue, 20 Jan 2004 04:47:48


A couple of things concern me Leif in your example before I get to your
answer.  Since you have posted pseudo code, I will try to do my best.

One of things about triggers is the Inserted and Updated tables in the
trigger are in fact tables and they can contain multiple records within
them.  As long as you are conscious of this you're ok.  Right now the way
you have written this code only one record from lets say 2 or more inserted
records would be written into the procedure.

Also, it is not good form to set a variable with the SELECT statement.  This
is proprietary to SQL Server it is much easier to understand and portable to
use the SET keyword to set variables.

Finally, its ok to put code into triggers.  You should only look to put code
in a procedure outside of trigger if you feel as though you may use it in
other places within your application or its highly complex and would benefit
being outside the trigger.  Otherwise, quick and concise code within a
trigger is ok.

With that out of the way let's discuss how to catch a specific event in
T-SQL

I will assume that data1a in Table 2 is unique or primary key for the table.

CREATE PROCEDURE usp_TestDaat

AS


BEGIN


END

With the IF statement in front of the Insert I make the determination as to
whether or not the variable is located within the table.  If its not then I
am going to insert the record, if the value does exist I won't do anything
at all.  This simple piece of code will ensure that a primary key violation
never occurs in this procedure.  This is proactive programming and I use it
all the time in my position as a SQL developer.  When I can catch something
like this, I generally set an output parameter to a number other then zero
and I give my developers definitions of each error value returned in the
output parameter.

My own error catch for transactional procedures goes like this:


BEGIN

 ROLLBACK TRANSACTION
 RETURN
END

Let's say you wanted to flag the error as its coming out of the procedure
you create the procedure like the following:

CREATE PROCEDURE usp_TestDaat


AS


BEGIN


END

ELSE
BEGIN


 RETURN
END


already within the table.

If I were to write this same code you posted, I would do it like what's
found bottom of this post.  My code has the added benefit of being able to
deal with multiple record inserts.  Again, this is just pseudo code you
posted so I can only write against what you posted.  However, perhaps it
will help you understand what I am trying to say.  Hope all of this helps
you and sorry for being a little long winded.  Proactive error catching
along with XML are two of my big topics in SQL Server.

Grant

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TI_Table1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

drop trigger [dbo].[TI_Table1]

GO

CREATE TRIGGER TI_Table1

            ON dbo.Table1

            FOR INSERT

AS

/*********************************HDR*************************************

*********GENERAL*********

OBJECT NAME:  TI_Table1

OBJECT TYPE:  Trigger

SUBCATEGORY:  Data Integrity

CREATED BY:   GRANT CASE

DATE:         01/18/2004

MODIFIED BY:  GRANT CASE

DATE:         01/18/2004

DESCRIPTION:

This trigger writes information from the table1 table on insert into

the Table2 table if it is not already located there.

*********VARIABLES*********

NAME                         TYPE               LOCAL/PASSED        DEFAULT

DESCRIPTION

---------------------------------------------------------------

*********DEPENDENCIES*********

TABLES:

OBJECTS:

*********TEMP TABLES*********

NAME       DESCRIPTION/USE

*********TRIGGER SPECIFIC*********

SOURCE TABLE:

DESTINATION TABLE:

CASCADED TRG:

CASCADED TRG TABLE:

*********REPORT SPECIFIC*********

REPORTS USING STORED PROCEDURE:

*********OUTPUT*********

*********MODIFICATION LOG*********

DATE      INITIALS    MODIFICATION

01/18/2004  GSC                    Created

*********HEADER CONVENTIONS*********

DO NOT GO PAST 75 CHARACTERS BEFORE GOING TO THE NEXT LINE

TO DEBUG, REPLACE ALL "-- DEBUG"

*********************************HDR*************************************/

--------------------------------------------------------------------------

-- LOCAL VARIABLE DECLARATION SECTION

--------------------------------------------------------------------------


--------------------------------------------------------------------------

-- LOCAL VARIABLE SET SECTION

--------------------------------------------------------------------------

--IF YOU DO NOT SET THIS, ACTIVE SERVER PAGES WILL HAVE PROBLEMS

SET NOCOUNT ON


            WHEN (SELECT COUNT(*) FROM DELETED) = 0 AND (SELECT COUNT(*)
FROM INSERTED) = 0 THEN 'None'

            WHEN (SELECT COUNT(*) FROM DELETED) > 0 AND (SELECT COUNT(*)
FROM INSERTED) > 0 THEN 'Update'

            WHEN (SELECT COUNT(*) FROM DELETED) = 0 AND (SELECT COUNT(*)
FROM INSERTED) > 0 THEN 'Insert'

            WHEN (SELECT COUNT(*) FROM DELETED) > 0 AND (SELECT COUNT(*)
FROM INSERTED) = 0 THEN 'Delete'

END

--------------------------------------------------------------------------

-- TEMP TABLE CREATION SECTION

--------------------------------------------------------------------------

--------------------------------------------------------------------------

-- CODE SECTION

--------------------------------------------------------------------------

INSERT INTO Table2

SELECT Data1a

FROM Inserted

LEFT OUTER JOIN Table2 ON Data1a = Data1

WHERE Data1a IS NULL


--------------------------------------------------------------------------

-- TESTING SECTION

--------------------------------------------------------------------------

--------------------------------------------------------------------------

-- CLEAN UP SECTION

--------------------------------------------------------------------------

GO

 
 
 

insert does not work when triggerd Stored Procedure fails

Post by Erland Sommarsko » Wed, 21 Jan 2004 01:34:24


[posted and mailed, v?nligen svara i nys]


> Can you give me an example of how to catch, or test for the error( I
> assume in the stored procedure)?   Is there something like if error>0
> else

I'm not surre where Grant was heading, his first post did not really
mean the same thing as his second.

Anyway, if you are in a trigger, any error that occurs aborts the
batch. So the only way to avoid this to happen is to write the
code, so that no error is raised. For instance, to avoid index
duplicated, you need to test with IF EXISTS before you INSERT.

When a stored procedure is called outside error context, you can in

detailed discussion on the topic, I have an article on my web site
about it: http://www.sommarskog.se/error-handling-II.html.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. Access Client does not work with INSERT and SELECT in same stored procedure

I have an Access project as client to SQL Server 2000. Client and server run
on the same development machine with Windows 2000 Professional, latest
servicepack.
SQL server 2000 has latest service pack 3 installed, Office 2000 (Access)
also has latest service pack 3. I am using Windows login to the SQL Server.

The Access client will not accept Insert and Select to the same table in one
stored procedure. The stored procedure returns nothing when called from the
Client,  but it returns a number of records if called locally from the SQL
Query Analyzer, or if I run the debug tool of the Query Analyzer. Here is my
procedure:

Create                                       PROCEDURE
TMSP_CustomerListSearchTest
AS
BEGIN

INSERT  dbo.TM_Export (OrgName) VALUES ('testorg')
Select dbo.TM_Export.orgname from dbo.TM_Export

END

Nothing is returned to the client when the client calls the stored
procedure. If I remove the INSERT line,   all records are returned to the
client. What could be wrong?

I have a number of stored procedures that works perfectly with the client,
however without the SELECT - INSERT case. It does not seem like my problem
is a login/security problem.

Regards

Tore Gylver

2. Natural Language Interface for datatbase query??

3. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

4. Need beta testers!

5. UPDATE otherdb:table fails from stored procedure, works other

6. Grid.Column.ControlSource change disables editing

7. UPDATE otherdb:table fails from stored procedure, works otherwise

8. Db triggering and java

9. Stored Procedure works but scheduled task fails - SQL 7.0

10. Insert Trigger and failed Stored Procedure

11. INSERT Failing In Stored Procedure

12. Stored procedure fails in esqlc prog but not in dbaccess

13. Merge subscription failed, could not find stored procedure??