cascade insert 1

[kafka]

I am a bit of a novice at SQL Server 7.0, and my attempts to write a cascade trigger that actually works have been fruitless.

I need to insert a new record with the identical value in MyTable2 every time a new unique id is generated in MyTable1 for column customer. For e.g. unique id with value 4 in column customer, is automatically generated when a new row is created in MyTable1. Simultaneously that record with a value of 4 for column customer2 should be entered in MyTable2.

I also need to ensure that MyTable2 locks and releases correctly to match a new insert in MyTable1 so we have matching increments in both tables. Can someone please show me the code for this. Thanks for all your help.

 

[tlbroadbent]

Your trigger should do something like this.

Create Trigger trgInsertNewCust
On MyTable1
For Insert AS

set nocount on
Insert MyTable2 (ID,col1,col2,col3,...)
Select ID,<val1>,<val2.,<val3>,...
From Inserted
Go

Using the Inserted table to obtain the ID guarantees that the ID will be the same on both tables. You supply column names and values! ;-) Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[kafka]

You are the man. Cheers. It Worked. One Question. What function does the 'nocount on' perform.

Best,

grateful sophomore

 

[kafka]

One more question:

Can I cascade the same vaule to multiple tables simultaneously? Thanks for your input.

 

[tlbroadbent]

&quot;SET nocount ON&quot; inhibits printing of the lines affected information in SQL Server. Triggers have a tendancy to cause errors if &quot;Lines Affected&quot; is printed because clients usually don't expect output from a trigger.

You can perform as many inserts and updates as needed to update multiple tables in the same trigger. Terry

http://members.home.net/tlbroadbent/prog.htm

&quot;I'm not dumb. I just have a command of throughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[mahaMutant]

tlbroadbent > you really are a great star! thanks for all the help you seem to offer everyone.

] always a sucker to the greatest lie of all: will only take a minute [