a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

If you want to determine type of trigger action from single trigger then see code below:

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...' -- set @.type = 'U'
else
if exists(select * from inserted)
print 'Insert...' -- set @.type = 'I'
else
print 'Delete...' -- set @.type = 'D'

... your code
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl

go

drop table tr_tbl

go

But it might be easier writing separate triggers. And you shouldn't query the base table T1 directly since you will get all rows in the table and not just the rows affected by the DML. You need to use the inserted/deleted tables based on the trigger action.

|||

This helps a lot, thanks,

One question: since there are more than one user on the system will there be a case that insert virtual table might have more than one row?

|||Yes, you should always write your trigger code such that it handles multiple rows. This will also force you to use simple set of DMLs from trigger for the logic which is more efficient than a procedural approach. If you want to enforce only singleton operations via DML then you can do so within trigger by checking for @.@.ROWCOUNT and rolling back the transaction/ DML operation.sql

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

you already have the code you need. you just need to put it in a trigger for which the syntax can be found in BOL.

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures an
d
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
ChrisChris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:

> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>
>|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:

> Well, try to drop the indexes during the insertion and later recreate them
.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.

> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Thanks for the replies. You have been helpful.|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:

>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures and
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
Chris
Chris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:

> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>
|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:

> Well, try to drop the indexes during the insertion and later recreate them.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.
|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.

> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Thanks for the replies. You have been helpful.
|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:

>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures and
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
ChrisChris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:
> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>> Chris
>> Do you have any indexes define on the table ?
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
>|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:
> Well, try to drop the indexes during the insertion and later recreate them.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.
> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris|||Thanks for the replies. You have been helpful.|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:
>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)