trigger help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger help

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-12-04 : 11:26:44

I have the following trigger. It works fine in if the fields in the marketing table are not null, but if they are then nothing is updated. Any help would be great.

alter trigger [dbo].[UpdateMarketingFromCalls] on [dbo].[MarketCalls] after insert
as
begin
update m
set m.contactdate = i.contactdate,
m.apptrep = i.apptrep,
m.contactstatus=i.callresult,
m.marketnotes = m.marketnotes + ' ' + i.apptrep + ' ' + cast(i.contactdate as nvarchar) + ': ' + i.notes
from inserted i
inner join marketing m on
m.marketing_id=i.marketing_id
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:39:21

quote:
Originally posted by blackX

I have the following trigger. It works fine in if the fields in the marketing table are not null, but if they are then nothing is updated. Any help would be great.

alter trigger [dbo].[UpdateMarketingFromCalls] on [dbo].[MarketCalls] after insert
as
begin
update m
set m.contactdate = i.contactdate,
m.apptrep = i.apptrep,
m.contactstatus=i.callresult,
m.marketnotes = coalesce(m.marketnotes,0) + ' ' + i.apptrep + ' ' + cast(i.contactdate as nvarchar) + ': ' + i.notes
from inserted i
inner join marketing m on
m.marketing_id=i.marketing_id
end

did you mean the above?

blackX
Posting Yak Master

102 Posts

Posted - 2008-12-04 : 11:46:10

quote:
Originally posted by visakh16

quote:
Originally posted by blackX

I have the following trigger. It works fine in if the fields in the marketing table are not null, but if they are then nothing is updated. Any help would be great.

alter trigger [dbo].[UpdateMarketingFromCalls] on [dbo].[MarketCalls] after insert
as
begin
update m
set m.contactdate = i.contactdate,
m.apptrep = i.apptrep,
m.contactstatus=i.callresult,
m.marketnotes = coalesce(m.marketnotes,0) + ' ' + i.apptrep + ' ' + cast(i.contactdate as nvarchar) + ': ' + i.notes
from inserted i
inner join marketing m on
m.marketing_id=i.marketing_id
end

did you mean the above?

The values from the instered table will never be null, however the values of m.apptrep, m.contactstatus, m.contactdate maybe and if they are the trigger will not update them to the values of the inserted row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:51:26

nope...i dont think thats right.it will update provided values in corresponding fields of inserted have not null values.

blackX
Posting Yak Master

102 Posts

Posted - 2008-12-04 : 11:52:57

quote:
Originally posted by visakh16

nope...i dont think thats right.it will update provided values in corresponding fields of inserted have not null values.

I wish it wern't true but that is what is happening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:59:44

nope...then there's somewhere else wrong. are you sure this is only trigger on table?

blackX
Posting Yak Master

102 Posts

Posted - 2008-12-04 : 13:12:22

quote:
Originally posted by visakh16

nope...then there's somewhere else wrong. are you sure this is only trigger on table?

yes

Maybe my logic is wrong so I will explain why I am trying to do. Upon inserting a new call record in the MarketCalls table (a child of the marketing table) I want the Marketing table to be updated to the values that were inserted into the MarketCalls record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources