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 repopulate table

Hi Guys,
I have transaction replication setup in my company, and in subscriber there
are some triggers there but not in publication, which is due to business
logic. Now I want to repopulate table's value from publication to subscrition
completely,but I don't want to rerun the snapshot because it will overrite
the triggers in the subscriber. How can do that? Thanks.
Do a no-sync subscription. I think you will want to change the article
properties so that in the snapshot tab, name conflicts section you select
delete all data in table, disable the trigger on the subscriber, run and
deploy the snapshot and then re-enable the trigger.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:ED7B6F2D-4CF7-477D-983A-1AA3740E5A51@.microsoft.com...
> Hi Guys,
> I have transaction replication setup in my company, and in subscriber
> there
> are some triggers there but not in publication, which is due to business
> logic. Now I want to repopulate table's value from publication to
> subscrition
> completely,but I don't want to rerun the snapshot because it will overrite
> the triggers in the subscriber. How can do that? Thanks.
>

About COLUMNS_UPDATED and .Net Applications

Hello All,
I am using COLUMNS_UPDATED in my triggers to audit changes on my tables. I
test everything with Query Analyzer and everything looks perfect.
But when I test the triggers with the applications written by our developers
(using Visual Basic or C#) looks like these applications are sending UPDATEs
including all the fields and COLUMNS_UPDATED shows always all the columns of
the record even if only one field was updated. Am I missing something? Or
maybe some recommendation to our developers?
Thanks,
Benjamin Nevarez
SQL Server Database AdministratorCOLUMNS_UPDATED doesn't tell you what data has changed, only what
columns are referenced in an UPDATE. So even if the developers did
things differently, COLUMNS_UPDATED still may not give you what you
want.
Also, depending on how you handle the values returned by
COLUMNS_UPDATED you may experience other problems. The column ordinal
numbers aren't always updated in a consistent manner whenever the table
structure changes.
For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
changed rows and columns by joining the INSERTED and DELETED tables
instead.
David Portas
SQL Server MVP
--|||Well, what I am doing is
1) Assign the field values from inserted and deleted tables to variables
like in
select @.inserted1 = field1, @.inserted2 = field2, ...
select @.deleted1 = field1, @.deleted2 = field2, ...
2) Manually compare the data like in
if (@.inserted1 = @.deleted1)
--do something
The problem here is that I need to do that for every field in every table.
Is there a faster way?
Thanks,
Ben
"David Portas" wrote:

> COLUMNS_UPDATED doesn't tell you what data has changed, only what
> columns are referenced in an UPDATE. So even if the developers did
> things differently, COLUMNS_UPDATED still may not give you what you
> want.
> Also, depending on how you handle the values returned by
> COLUMNS_UPDATED you may experience other problems. The column ordinal
> numbers aren't always updated in a consistent manner whenever the table
> structure changes.
> For these reasons I recommend you avoid COLUMNS_UPDATED. Identify
> changed rows and columns by joining the INSERTED and DELETED tables
> instead.
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:

>Well, what I am doing is
>1) Assign the field values from inserted and deleted tables to variables
>like in
> select @.inserted1 = field1, @.inserted2 = field2, ...
> select @.deleted1 = field1, @.deleted2 = field2, ...
>2) Manually compare the data like in
> if (@.inserted1 = @.deleted1)
> --do something
>The problem here is that I need to do that for every field in every table.
Hi Ben,
No, that's not the problem. The main problem is that your trigger will
break on any multi-row update. A trigger gets fired once per execution
of an UPDATE statement, not once per row affected, so the trigger code
should be able to handle one row affected as well as no rows affected or
1000 rows affected.
To find out what data actually changed, you must:
a) Take steps to ensure that the primary key columns of your table are
never changed in an UPDATE statement - these columns are the only way to
match rows from the inserted and deleted pseudo-tables;
b) Use the following FROM and WHERE clause as part of a query that
handles the rows in which the value for Column01 actually changed
(assuming the primary key consists of columns KeyCol01 and KeyCol02)
FROM inserted AS i
INNER JOIN deleted AS d
ON d.KeyCol01 = i.KeyCol01
AND d.KeyCol02 = i.KeyCol02
WHERE i.Column01 <> d.Column01
c) If Column01 allows NULLS, change the WHERE clause to
WHERE COALESCE (NULLIF (i.Column01, d.Column01),
NULLIF (d.Column01, i.Column01)) <> NULL
Of course, you still have to do this for all relevant columns. Yes,
programming sometimes involves lots of tedium.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually I wrote one solution with a simple join ... and NULLIF. This does
everything in one step. The insert is something like this
insert into audittable
select i.pk, nullif(i.col1, d.col1), nullif(i.col2, d.col2), nullif(i.col3,
d.col3), system_user, getdate()
from inserted i inner join deleted d on i.pk = d.pk
Thanks All,
Ben
"Hugo Kornelis" wrote:

> On Thu, 3 Mar 2005 10:27:02 -0800, Benjamin Nevarez wrote:
>
> Hi Ben,
> No, that's not the problem. The main problem is that your trigger will
> break on any multi-row update. A trigger gets fired once per execution
> of an UPDATE statement, not once per row affected, so the trigger code
> should be able to handle one row affected as well as no rows affected or
> 1000 rows affected.
> To find out what data actually changed, you must:
> a) Take steps to ensure that the primary key columns of your table are
> never changed in an UPDATE statement - these columns are the only way to
> match rows from the inserted and deleted pseudo-tables;
> b) Use the following FROM and WHERE clause as part of a query that
> handles the rows in which the value for Column01 actually changed
> (assuming the primary key consists of columns KeyCol01 and KeyCol02)
> FROM inserted AS i
> INNER JOIN deleted AS d
> ON d.KeyCol01 = i.KeyCol01
> AND d.KeyCol02 = i.KeyCol02
> WHERE i.Column01 <> d.Column01
> c) If Column01 allows NULLS, change the WHERE clause to
> WHERE COALESCE (NULLIF (i.Column01, d.Column01),
> NULLIF (d.Column01, i.Column01)) <> NULL
>
> Of course, you still have to do this for all relevant columns. Yes,
> programming sometimes involves lots of tedium.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>