Hi,
I ahve the followign trigger. In this I want to set the 2 Insert statements
at the bottom, to execute concurrently. How can I do that?
CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
FOR INSERT
AS
DECLARE @.CompOrderNo VarChar(30)
DECLARE @.Sno int
DECLARE @.EndUser varchar(25)
DECLARE @.ESN varchar(20)
DECLARE @.MinNo varchar(12)
SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
inserted --where CompanyORderNo is null
UPDATE WyethDataCard
SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanySno
)
VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
INSERT INTO EndUserChangeHistory
(MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
VALUES ( @.CompOrderNo,
@.ESN,
@.MinNo,
@.EndUser,
'Database Import / Manual Insert',
GetDate()
)
Is there some statement like CONCURRENT EXEC to achieve this?
Thanks
pmudWhy do you want to make them concurrent? Even if the machine has several pro
cessors, there's no
guarantee that this will happen at the same time. If you want both or nothin
g, you should have some
error handling in the trigger (see the articles on error handling at www.sommarsko
g.se).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert statement
s
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanyS
no)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||Hi Tibor,
Actually, by concurrent execution, I meant parallel execution of the 2
statements. Is tehre any way I can set parallel execution fo teh 2 statement
s
rather than having one after the other?
Thanks
--
pmud
"Tibor Karaszi" wrote:
> Why do you want to make them concurrent? Even if the machine has several p
rocessors, there's no
> guarantee that this will happen at the same time. If you want both or noth
ing, you should have some
> error handling in the trigger (see the articles on error handling at www.sommars
kog.se).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>|||Do you mean that the inserts should be atomic or within a single
transaction?
If so, then refer to this document, specifically the section titled
"Database Transactions".
http://msdn.microsoft.com/library/d...ent.
asp
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus (
> Company_OrderNo,Status_ID,Created_Date,C
ompanySno)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||no
why?
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B0D56D29-D70C-4684-9F7F-0957CBFF6443@.microsoft.com...
> Hi Tibor,
> Actually, by concurrent execution, I meant parallel execution of the 2
> statements. Is tehre any way I can set parallel execution fo teh 2
> statements
> rather than having one after the other?
> Thanks
> --
> pmud
>
> "Tibor Karaszi" wrote:
>|||Is there any tutorial or book you rae aware which I can read to quickly come
up to speed with transctions and how they are implemeted?
Actually, I am aware of the basic concept of trnsctions but have never
implemeted them.
Do you have any suggestions for me to get up to speed Quickly?
Thanks
--
pmud
"JT" wrote:
> Do you mean that the inserts should be atomic or within a single
> transaction?
> If so, then refer to this document, specifically the section titled
> "Database Transactions".
> http://msdn.microsoft.com/library/d...en
t.asp
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>
>|||You will not need a book to understand the concept of basic transactions.
Database transaction:
http://en.wikipedia.org/wiki/Database_transaction
BEGIN TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
COMMIT TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
ROLLBACK TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
The simplified sample below attempts to transfer a balance of $100 between 2
accounts. If all goes well, the transaction is committed (saved) and the
value of 1 is returned. If there is an error, then the transaction is rolled
back (cancelled), and a value of -1 or -2 is returned. By executing both
updates within a transactions, we insure that the work is either all saved
or all cancelled and the accounts are balanced.
begin transaction
update Accounts set balance = balance - 100 where acct_number = 4625
if @.@.error <> 0
begin
rollback transaction
return -1
end
update Accounts set balance = balance + 100 where acct_number = 4650
if @.@.error <> 0
begin
rollback transaction
return -2
end
commit transaction
return 1
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
> Is there any tutorial or book you rae aware which I can read to quickly
> come
> up to speed with transctions and how they are implemeted?
> Actually, I am aware of the basic concept of trnsctions but have never
> implemeted them.
> Do you have any suggestions for me to get up to speed Quickly?
> Thanks
> --
> pmud
>
> "JT" wrote:
>|||Thanks JT. That is definitely helpful. :)
--
pmud
"JT" wrote:
> You will not need a book to understand the concept of basic transactions.
> Database transaction:
> http://en.wikipedia.org/wiki/Database_transaction
> BEGIN TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
> COMMIT TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
> ROLLBACK TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
> The simplified sample below attempts to transfer a balance of $100 between
2
> accounts. If all goes well, the transaction is committed (saved) and the
> value of 1 is returned. If there is an error, then the transaction is roll
ed
> back (cancelled), and a value of -1 or -2 is returned. By executing both
> updates within a transactions, we insure that the work is either all saved
> or all cancelled and the accounts are balanced.
> begin transaction
> update Accounts set balance = balance - 100 where acct_number = 4625
> if @.@.error <> 0
> begin
> rollback transaction
> return -1
> end
> update Accounts set balance = balance + 100 where acct_number = 4650
> if @.@.error <> 0
> begin
> rollback transaction
> return -2
> end
> commit transaction
> return 1
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
>
>|||pmud (pmud@.discussions.microsoft.com) writes:
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements at the bottom, to execute concurrently. How can I do that?
You can't, and there would be no point with it.
On the other hand:
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
This is not going to work out well. Triggers in SQL Server fires
once *per statement*, so the inserted table can hold many rows, which
your trigger fails to handle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Rafael Salas wrote: