help the logic - 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

help the logic

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-05-03 : 01:26:35

I have to write trigger to relate two table.

If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.

How this can be done, do we need to point the common key fields in both table while inserting

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 01:38:41

yes. You can read up Books OnLine on Triggers.

Something like this


create trigger table1_trigger on table1 for insert
as
begin
    insert into table2(col1, col2, col3)
    select col1, col2, col3
    from   inserted
end

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-03 : 01:41:24

you need to create trigger which looks like this

Create Trigger TrgName
for Insert
As
Begin
Insert Tbl_History
Select * From Inserted
End

Read on Book online for more details..under triggers..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-03 : 01:55:14

What is the need of having two tables with same structures and data?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-03 : 02:24:27

fail safe?

i think it's just an example Maddy to get him starting on the more complex task of audit trail

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 02:48:16

Are you implementing audit trail ?
Refer to here
http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html
http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-05-04 : 02:39:38

Thanx for you help. What I have to do means matching some fields with table2 if the matches found in table1 side then only the table2 have to updated.

So I have used Stored procedures to done Insert and update.

senthil

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-04 : 08:18:15

ah, more like referential integrity to me...

you need to handle this in your sproc when you do the insert or update

like

update tablename
set field1=@newvalue
where exists(select * from tablesource where pk=@newvaluePK)

--------------------
keeping it simple...

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-05-08 : 07:21:54

Exactly Jen.

Senthil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources