update after a change - 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

General SQL Server Forums

New to SQL Server Programming

update after a change

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-27 : 05:58:24

Hi,

I have two tables (test, test_invoice) both filled in automatically; what i need to do is update whenever there has been a change in table test_invoice (literally new record with date of invoice).

two tables:


create table test
(id_customer int not null
,date1 smalldatetime
,date2 smalldatetime
,flag char(3)
)
ALTER TABLE [dbo].[test] ADD
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
 (
 [id_customer]
 ) ON [PRIMARY]
GO

insert into test (id_customer, date1, date2, flag) values (2323, '2008-2-23', '', 'Yes')
insert into test (id_customer, date1, date2, flag) values (2325, '2008-2-25', '', 'No')

create table test_invoice
(id_customer int not null
,date_invoice smalldatetime
)

ALTER TABLE [dbo].[test_invoice] ADD
 CONSTRAINT [PK_test_invoice] PRIMARY KEY CLUSTERED
 (
 [id_customer]
 ) ON [PRIMARY]
GO

insert into test_invoice (id_customer, date_invoice) values (2325, '2008-3-2')
insert into test_invoice (id_customer, date_invoice) values (2329, '2008-3-5')

so how do i make this update happening automatically whenever there is a new record in test_invoice


update t
set t.date2 = ti.date_invoice
from	
		test as t
	inner join test_invoice as ti
	on ti.id_customer = t.id_customer

I'd say IF is missing in front of the update sentance...? :-)

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 06:26:15

Create an update trigger on test_invoice and Put the update code inside the trigger.

CREATE TRIGGER test_invoice_u
ON test_invoice
AFTER UPDATE
AS
BEGIN
update t
set t.date2 = ti.date_invoice
from	test as t
inner join INSERTED as ti
on ti.id_customer = t.id_customer
END

this ensures each time when update is performed on test_invoice the trigger is fired and it will update that record of test whose id is same as id of currently updated record (which we get from INSERTED table).

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-27 : 06:49:27

Visakh, thank you.

i just changed "ON test_invoice AFTER UPDATE" to "ON test_invoice AFTER INSERT" and it does the magic.

p.s.: is there any way to have three of four triggers sequentially checking for new inserts/updates etc? or is it better to put them in a job?

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 06:56:58

quote:
Originally posted by slimt_slimt

Visakh, thank you.

i just changed "ON test_invoice AFTER UPDATE" to "ON test_invoice AFTER INSERT" and it does the magic.

p.s.: is there any way to have three of four triggers sequentially checking for new inserts/updates etc? or is it better to put them in a job?

thank you

ok so you wanted it for inserts then.
Didnt get the necessity for three or four triggers. You can have a single trigger for all the actions in a table just give AFTER INSERT,UPDATE,DELETE and trigger gets fired automatically each time any of action takes place on table. You dont require any special job for firing triggers as it automatically monitors the base table and gets fired when specified action takes place.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-27 : 06:58:31

great! Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 07:06:25

quote:
Originally posted by slimt_slimt

great! Thank you.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources