About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

ABOUT sql server 2005 partition

hello,

We are writting an erp for group,and want to use partition of sql server 2005 for large data,our environment is window 2003 server,8g memory.my one solution will create many physical files(about 500-600) to fit 500-600 partitions,how about performance? or I will use another arithmetic in order to reduce partitions?

Hi,

Wondering why you will need that many partitioons for. How big your table is likely to be ? how many rows? What are planning to use as you partition key?

Jag

|||

hello,

Thank you for your attention,following are our case:

we wrote an erp for a group,it have five child companys,three factorys,six trademark,we finish distribution sale,supply chain,production(not running),our database size now is 19G on sql server 2000,the system is slow,we now upgrading hardware and software,database will use sql server 2005.

I analyse the system, if I use partition to divide trademark,customer data and warehouse data and supplier data,producing,and every child company also will use partition,I think it will be better more,if a company have 10 partitons, five companys will reach 50 partitions,I really not know how many partitions will right,and performace is best?

I also want to do an online system for small company,if using design above,50 companys will up to 500 partitions,I think this case,the arithmetic will different from above,it will not grow with company amount,is right?

Thank you,again.

|||

It really seems like that is a lot of partitions, especially for a database that is only 19gb. Really, the idea behind using partitions is somewhat two-fold. First, you can segreate your archive data from your live data for performance reasons. Also, you can segment your historical or archive data itself for performance benefit. You can also move data from partition to partition as needed. However, there is a limit the number of partitions you can create, and as the number of partitions grows, the amount of administration grows. This leads to the type of hardware you are using.....are you using a SAN or some sort of high-performance shared disk storage? If so, what RAID level are you using? If this design is optimal, it might be time to look at how your application is written. Most likely, you will see the highest performance gain by slight design changes and query optimizations as compared to hardware upgrades and partitioning.

Tim

|||

hello,

thank you for your suggest,I learn a lot.

The company have two system,one for sale,one for purchase,we now will incorporate these system,incorporation size are at least 30g,retail system are working recently,all shops are about 400 amount,so data will grow faster than before,at next quater,the production system will begin,also will produce so much data,we use raid5,donn't use SAN,Perhaps I need study more knowledge about SAN.

we upgrade hardware and software,the old system is slow,often happen concurrentcy problem,the erp first is bought ,but couldn't fit the company,the architecture is very bad,we do a great lot of redevelopping,now I redesign architecture,consider optimization at transation,query,analyse,and in order to predigest the communication between company, customer, warehouse,factory,supplyer, I put some table together to one table,using partition to divide them,I think it will have good performance for transation, and persormance for group report,my design have consider about partition administrator, but as you have mentioned,partition for history data, yes,it will good for performance,there is another solution, I want to design an OLAP system for histotry analysing,is it right?

at last,how much about partition limit? thank you