interesting update/insert trigger problem (null issue)

Ok so here is the issue. I am thinking I somehow have to clear the old data
after executing the trigger.
So I add a user Joe Brown with his info on a users table, a trigger fires
and dumps the duplicate data into a users-dup table (for other justifiable
purposes). Update does the same basic thing.
Works fine. Here is the problem. I then add another name. Jack Black and
his info, but he has some null values... like i don't know his address. So
now the trigger fires and all of the duplicate data is carried across to the
dup table... except where there was no data (NULL) in a field... it is
adding the last "real" data set in replace of the null. SO Jack Black has
Joe Brown's address in his field... since it was the last "not null" value
entered in that column.
Looking for ideas. Figured it is something simple, I am just missing. Like
some sort of purge call.
Below is the code I am using:
for insert:
CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
FOR INSERT
AS
insert into user_marketing (greeting, fName, lName, title, compName,
address, city, provState, fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, fk_userID)
select greeting, fName, lName, title, compName, address, city, provState,
fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, pk_userID
FROM Inserted
for update:
CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
FOR UPDATE
AS
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM user_marketing a, users b
where a.fk_userID=
(SELECT pk_userID
FROM Inserted)
Thanks!Two questions.
1 - Why do you want to do this?
2 - How can we identify the last "real" data inserted in the table?, How do
you know it is "real" and not a fake like you are trying to do?
AMB
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Hi
Triggers are executed per statement, which can update multiple rows. Using
where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
arbitrary value. You are also not relating user_marketing to users
To keep user_marketing in step try:
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM dbo.user_marketing a
JOIN Inserted b ON b.pk_userID = a.fk_userID
John
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Thanks John...
That worked. Duh...
"John Bell" wrote:
> Hi
> Triggers are executed per statement, which can update multiple rows. Using
> where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
> arbitrary value. You are also not relating user_marketing to users
> To keep user_marketing in step try:
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM dbo.user_marketing a
> JOIN Inserted b ON b.pk_userID = a.fk_userID
> John
>
> "cheezebeetle" wrote:
>