SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Procedure not executing in trigger


Procedure not executing in trigger

Author
Message
pankaj.kuchaliya
pankaj.kuchaliya
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 234
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 .
Lowell
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123302 Visits: 41447
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
pankaj.kuchaliya
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 234
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370047 Visits: 46950
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
pankaj.kuchaliya
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 234
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
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123302 Visits: 41447
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
drew.allen
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24401 Visits: 11756
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search