Procedure not executing in trigger

Question

Post reply

Procedure not executing in trigger

pankaj.kuchaliya

SSC Eights!

Points: 944
More actions
September 22, 2010 at 10:01 pm

#227167

Hi,
I have create the procedure to insert the data from one database to another database.
and create a trigger which execute that procedure. but when the trigger is fire then it display the "The transaction ended in the trigger. The batch has been aborted".
Please help me.
Trigger :-
alter trigger testpktest on ETL_Update
AFTER INSERT, UPDATE, DELETE
as
declare @frmdt as varchar(10) ,@todt as varchar(10)
begin try
select @frmdt=From_dt,@todt=To_dt from inserted
print @frmdt print @todt
exec [sp_ETL_To_Rpt] @frmdt,@todt
end try
begin catch
select convert(varchar, ERROR_NUMBER()) , Convert(varchar, ERROR_SEVERITY()) , Convert(varchar, ERROR_STATE())
,ERROR_PROCEDURE() ,Convert(varchar, ERROR_LINE()) ,ERROR_MESSAGE()
end catch
Table Script:-
CREATE TABLE [dbo].[ETL_Update](
[ID] [int] IDENTITY(1,1) NOT NULL,
[From_dt] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[To_dt] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Transaction_dt] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
and i have created a procedure which is called in trigger .

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

what would happen if your procedure received NULL for either of the two parameters? could it raise an error?

exec [sp_ETL_To_Rpt] @frmdt,@todt

since your trigger says it's also for DELETE, and you are only assigning variables from the INSERTED table, that would happen in at least one scenario; it also depends on your data; are you sure the two values receive a value in the isnert or update?

also, technically INSERTED might have more than one row, and you are assigning the values to your local params from what could be a collection of values...does it matter to your business model? or does the business always insert,update or delete only a single row at a time?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 2

Lowell (9/22/2010)
what would happen if your procedure received NULL for either of the two parameters? could it raise an error?
exec [sp_ETL_To_Rpt] @frmdt,@todt
since your trigger says it's also for DELETE, and you are only assigning variables from the INSERTED table, that would happen in at least one scenario; it also depends on your data; are you sure the two values receive a value in the isnert or update?
also, technically INSERTED might have more than one row, and you are assigning the values to your local params from what could be a collection of values...does it matter to your business model? or does the business always insert,update or delete only a single row at a time?

I have assigned default value, so if null is send then default value is assigned.

is it necessary to define the statement for deleted or not , or i have to create a trigger for only insertion.

i have to insert near about 5-7k row from different database to current database by using SP. which have been called in Trigger on the basis of date.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

pankaj.kuchaliya (9/22/2010)
i have to insert near about 5-7k row from different database to current database by using SP. which have been called in Trigger on the basis of date.

Now that's a problem. The trigger is written assuming that there is only ever 1 row in the inserted table. If you insert more than one row in a single statement, that trigger is not going to work correctly.

What's the definition of the proc?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankaj.kuchaliya SSC Eights! Points: 944 More actions · Answer 4

GilaMonster (9/22/2010)
pankaj.kuchaliya (9/22/2010)
i have to insert near about 5-7k row from different database to current database by using SP. which have been called in Trigger on the basis of date.
Now that's a problem. The trigger is written assuming that there is only ever 1 row in the inserted table. If you insert more than one row in a single statement, that trigger is not going to work correctly.
What's the definition of the proc?

K,

I have table ETL_Update(fromdate,todate,transactiondate) and i have write the trigger when any row inserted in this table. and i execute the SP "[sp_ETL_To_Rpt_22Sept]" in Trigger which have two parameter "fromdate" & "todate". on the basis of these date i insert 5-7k rows one table to anoter table.

I have one confusion that is "trigger is executed when i insert in " ETL_Update" table . only one row can be inserted in this table at a single time. but in trigger we can insert multiple row in other table.

Lowell SSC Guru Points: 323504 More actions · Answer 5

pankaj.kuchaliya (9/22/2010)
I have one confusion that is "trigger is executed when i insert in " ETL_Update" table . only one row can be inserted in this table at a single time. but in trigger we can insert multiple row in other table.

the behavior of a SQL Trigger is different than say, Oracle Triggers.

In Oracle, for exmaple, the trigger is fired once for each row....so an insert operation that has 5K rows fires the trigger 5K times.

SQL is different. the trigger fires once per batch..a insert into...select from that has 5k rows fires the trigger just once...so the design of the trigger has to take that into account.

as a general rule, if you've declared a variable inside a SQL trigger, the trigger is probably not written for a set based operation,at least if it is getting a value from either the INSERTED or DELETED tables.

since your calling a procedure inside that trigger, it will be called once per batch, not once for each row; that is why we asked if your business practice would only allow one row to be manipulated at a time; If that were true, while your trigger is not designed around a set based operation, it would at least infer that the call to the proc is acceptable based on your business practice.

any operation you do in a trigger should be written assuming more than one row is being inserted; most likely the logic withing the procedure being called will need to be moved inside the trigger instead of as a procedure call in order to affect multiple rows at a time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76944 More actions · Answer 6

pankaj.kuchaliya (9/22/2010)
when the trigger is fire then it display the "The transaction ended in the trigger. The batch has been aborted".
Please help me.

Everything within a trigger--including calls to stored procs and dynamic SQL--participates in the same transaction as the event that triggered it. If any of those issues a GO or COMMIT WORK it will prematurely end the main transaction causing this error message. The same thing will happen if you issue a COMMIT TRAN without creating a corresponding subordinate transaction.

If you are using dynamic SQL, you CANNOT use GO statements within your dynamic SQL. If your stored proc is issuing a COMMIT, you probably haven't created a subordinate transaction.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA