better method for DTS: triggers

Showing posts with label triggers. Show all posts

Friday, March 30, 2012

Is triggers multi-threaded?

Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes any
>> kind of network communication or file IO, regardless of whether the code
>> is VB6 COM or TSQL or CLR code in 2005.
>> David
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Hi David
>> STAs can certainly be launched from MTAs but the bigger problem with VB6
>> COM components is that they use TLS which isn't designed to work with SQL
>> Server's UMS. Under load, this can lead to various possible undesirable
>> conditions such as inconsistent results, crashes & hung UMS schedulers
>> (at least in SQL2K).
>> If the original poster really HAS to create a component under a trigger,
>> my advice is to use sp_OACreate only if the trigger activity is low (as
>> developing in VB does have its advantages in terms of productivity). If
>> the trigger activity is high (meaning it has a high probability of being
>> heavily pre-empted & re-scheduled), consider either a different
>> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
>> with delphi or c.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many
>> records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes
>> any kind of network communication or file IO, regardless of whether the
>> code is VB6 COM or TSQL or CLR code in 2005.
>> David
>>
>sql

Is triggers multi-threaded?

Wednesday, March 21, 2012

Is this correct use of INSTEAD OF Triggers?

I am loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. When the process is
finished, I'll have only good rows in B, and exeption rows in C.

I am investigating INSTEAD OF triggers, however my question to the
group is, is there a better or best practice for this scenario? This
must be common. Any high-level tips or direction will be highly
appreciated.

DAP>> loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. <<

You might want to use a cursor that attempts to insert each A row into
B and throws the exceptions into C. This would give you better control
and perhaps a chance to fix the bad rows with (ugh!) procedural code.

A moire set-oriented approach woudl be to create a VIEW on A whch has
the B constraints:

CREATE VIEW GoodA
AS SELECT *
FROM A
WHERE << B's constraints as predicates>> ;

You are probably thinking that the next step is to use:

CREATE VIEW BadA
AS SELECT *
FROM A
WHERE NOT (<< B's constraints as predicates>>);

But this does not work. A CHECK() constraint will accept an UNKNOWN
result from its predicate; a WHERE clause will reject them. You will
have to write a little extra code in each predicate to handle NULLs.

example:

CREATE TABLE B
( ..
foo INTEGER CHECK ( foo >= 0), -- works for null
..);

SELECT *
FROM A
WHERE ( foo >= 0 OR foo IS NULL);|||Hi

You can try something like:

INSERT INTO Table C
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINT FAIL
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINTS PASS
OR
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)

John

"Dan" <dpratte@.dpratte.com> wrote in message
news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common. Any high-level tips or direction will be highly
> appreciated.
> DAP|||Dan (dpratte@.dpratte.com) writes:
> I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common.

Not really.

I think the only way to do this without duplicating the constraints is
run a cursor one-by-one as suggested by Celko. An improvement could be
to first attempt to insert all, and if there is an error, use the
cursor as a fallback. But you could not do this in an INSTEAD OF
trigger, because an error in a trigger aborts the batch. You see,
the whole idea is that the INSERT statement should be atomic, either
all rows make it, or others not.

An alternative would be move the constraints to the trigger and check
for them there. An INSTEAD OF trigger would then redo the original
INSERT statement for the good rows. An AFTER trigger would delete
the bad rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I missed the title to this! Rather than use a trigger I would put the logic
into a stored procedure.

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:427d0150$0$1878$da0feed9@.news.zen.co.uk...
> Hi
> You can try something like:
> INSERT INTO Table C
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINT FAIL>
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINTS PASS>
> OR
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)
> John
> "Dan" <dpratte@.dpratte.com> wrote in message
> news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>>I am loading data from table A into table B. Certain columns in B have
>> check constraints. I'd like for any rows from A, which violate
>> constraints, to be inserted into a third table, C. When the process is
>> finished, I'll have only good rows in B, and exeption rows in C.
>>
>> I am investigating INSTEAD OF triggers, however my question to the
>> group is, is there a better or best practice for this scenario? This
>> must be common. Any high-level tips or direction will be highly
>> appreciated.
>>
>> DAP
>>

Friday, March 30, 2012

Is triggers multi-threaded?

Is triggers multi-threaded?

Is triggers multi-threaded?

Wednesday, March 21, 2012

Is this correct use of INSTEAD OF Triggers?

better method for DTS

Blog Archive

About Me