Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Procedure not executing in trigger Expand / Collapse
Author
Message
Posted Wednesday, September 22, 2010 10:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 3:58 AM
Points: 42, 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 .
Post #991690
Posted Wednesday, September 22, 2010 10:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:56 PM
Points: 12,921, Visits: 32,286
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #991695
Posted Wednesday, September 22, 2010 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 3:58 AM
Points: 42, 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.
Post #991730
Posted Wednesday, September 22, 2010 11:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #991732
Posted Wednesday, September 22, 2010 11:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 3:58 AM
Points: 42, 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.
Post #991738
Posted Thursday, September 23, 2010 4:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:56 PM
Points: 12,921, Visits: 32,286
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #991887
Posted Thursday, September 23, 2010 10:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #992202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse