| Author |
 Topic  |
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
 Posted - 05/06/2012 : 06:29:39
|
Hi there,
I have created a trigger on a table to execute a store proc when a record is inserted. But if you insert a record to this table you have to wait until the store proc is executed. Is there a way that you can send the data to the table and continue with your work and the database will realize there is a new record, take the required info from the record and execute the store procedure in the backend without interrupting your application so the user can carry on working.
ALTER TRIGGER [dbo].[Exec_SP_PerformanceManagement]
ON [dbo].[PerformanceManagement_Execute]
after INSERT
AS
/* Declare variable to be sent to the SP */
DECLARE
@tActivity varchar(50),
@tHierarchy varchar(50),
@tProdMonth varchar(50),
@tWhoOrgUnit varchar(50),
@tRowID int
begin
Set @tRowID = (Select RowID from inserted)
Set @tActivity = (Select Activity from inserted)
Set @tHierarchy = (Select Hierarchy from inserted)
Set @tProdMonth = (Select ProdMonth from inserted)
Set @tWhoOrgUnit = (Select WhoOrgUnit from inserted)
execute [SP_PerformanceManagement]
@Activity = @tActivity,
@Hierarchy = @tHierarchy,
@ProdMonth = @tProdMonth,
@WhoOrgUnit = @tWhoOrgUnit
Delete from PerformanceManagement_Execute where RowID = @tRowID
end |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/06/2012 : 06:47:44
|
No, SQL Server does not let you do that. The insert statement and the code in the trigger are executed in one transaction, and both will succeed or fail together. In that sense, you can consider the insert statement and the trigger to be part of a single atomic operation.
If you have the opportunity to do so, what you might want to do is to examine why the stored proc is taking a long time, and whether those operations are really required as part of the insert. When you run into that type of situations, in many cases, that may be the result of poor design choices and could be improved by making better design choices. |
Edited by - sunitabeck on 05/06/2012 06:49:09 |
 |
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 05/06/2012 : 07:51:37
|
Thanx I understand, but is there no other way? Maybe creating a job to execute every 5 minutes or something else?
Yes maybe the SP can be tuned to run little bit faster but remember for the end user having to wait 30 seconds - 5 minutes depending on the amount of records the SP has to process make them irritated when they can continue to work in the front end doing other work than to wait for the SP to complete in the front-end
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/06/2012 : 08:05:59
|
You can certainly remove the trigger and schedule an agent job to run at regular intervals.
More often than not, when people set up a trigger (rather than a scheduled job or some other types of updates) that is to make sure that the insert and the tasks done in the trigger go hand in hand (to ensure data integrity, implement essential business logic etc.)
So before you decide to use a scheduled task to run the stored proc, you should examine the code in the stored procedure to understand what impact that would have on data integrity and business logic.
If you want to create an agent job, it is fairly simple: http://msdn.microsoft.com/en-us/library/ms190268.aspx
One other thing to note in the trigger code that you currently have is that it makes an assumption that only one row will be inserted for each insert operation. This is not necessarily true - for a given insert operation, there may be multiple rows inserted. Trigger code will get invoked only once for each insert, regardless of the number of rows inserted. In such cases, the behavior of the trigger code that you currently have may not be correct. |
|
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 05/06/2012 : 08:14:58
|
Thanx for the info,
Yes the trigger was designed this way to only capture one record at a time as the application (front -end) will only send the value to the "temp" table once everything is 100% done. The application send a new record to the "temp" table if data changes so the SP will re-create the data output for the reports again. The data can only be changed from the front-end and this will then be updated in the back-end. |
|
|
|
jeffw8713
Aged Yak Warrior
USA
694 Posts |
Posted - 05/06/2012 : 09:33:48
|
| There is a method to do this without an agent job so the system takes care of it for you. Lookup Service Broker in Books Online. This can be setup to start your stored procedure on insert of data - which would be queued in the service broker. |
|
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 05/06/2012 : 10:12:09
|
| Thanx I will read up and check it out. I have never worked with Service Broker before |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 05/06/2012 : 12:53:58
|
Be careful with service broker as it works in asynchronous mode.So don't be surprised not to see the records which you were expecting to see in the result set.
After Monday and Tuesday even the calendar says W T F .... |
|
|
| |
Topic |
|
Help with Trigger execution time
