How to insert/del/upd rowdata into the same table

Question

Post reply

How to insert/del/upd rowdata into the same table

Nick-272488

SSC Rookie

Points: 42
More actions
October 26, 2005 at 5:39 am

#88333

Hello everybody,
I'm using sql server 2000 and i want
when insert a row into table1 automatically create the same row into the same table (with some values changed by me).
when delete a row into table1 automatically delete the other row-s into the
same table
when update a row then update the other rows ...
i dont know how to implement such a thing and i don't know if it is possible
without deadlocks etc.
Any comments will be great......
Thanks in advance
Nick
country greece

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the table definition, sample data and the required reaction for each operation.

gbn SSCertifiable Points: 6429 More actions · Answer 2

A trigger for each operation.

Insert is quite straight forward

For update and delete, you'd need to extract the primary key for the modified/deleted row and use that to drill to your own rows, which will also have their own PK...

So:

1. Are you sure your design is correct?

2. Are your own rows children of the original row (by an FK value in child row back to parent row OK, this is NULL in parent) - this allows you to spot ypor own rows

3. Should your own rows be in another table?

Basically, it can be done, but probably shouldn't be done...

Nick-272488 SSC Rookie Points: 42 More actions · Answer 3

HELLO,

suppose that i have the following table structure

CREAT

create TABLE [dbo].[TEST] (

[ID] [int] NOT NULL ,

[varchar] (25),

[DESCR] [varchar] (255) COLLATE Greek_CI_AS NULL ,

[COMID] [int] NOT NULL ) ON [PRIMARY]

GO

then when a record insert in the test table

eg

INSERT INTO TEST VALUES (1,'ITEMID1','ITEMDECRIPTION',100)

i want to automatic insert

(2,'ITEMID1','ITEMDECRIPTION',200) --same itemid and description

(3,'ITEMID1','ITEMDECRIPTION',300) --same itemid and description

(4,'ITEMID1','ITEMDECRIPTION',400) --same itemid and description

and the same with update when i change the record 2 description

eg (2,'ITEMID1','descrxxxxxx',200) --same itemid but description changed

i want to update the other records

(1,'ITEMID1','descrxxxxxx',100)

(3,'ITEMID1','descrxxxxxx',300)

(4,'ITEMID1','descrxxxxxx',400)

and finally when i delete 1 of 4 i want to delete all 4 records from table.

Thanks

Nik

gbn SSCertifiable Points: 6429 More actions · Answer 4

As you have described it, your only link between rows is non key columns.

There is key field to identify the "child" rows linked to the "parent rows"

This breaks all manner of database rules.

You could match on "DESCR" column, say, but this isn't your key.

This leads to performance issues, table scans, and you'll be posting in 3 months saying "it's really slow".

Now: Add a column to you table called "ParentID", nullable, and add and FK from that to the PK and you'll have salvaged something. This restores the link between your rows.

INSERT INTO TEST VALUES (1,'ITEMID1','ITEMDECRIPTION',100, NULL)

i want to automatic insert

(2,'ITEMID1','ITEMDECRIPTION',200, 1) --same itemid and description

(3,'ITEMID1','ITEMDECRIPTION',300, 1) --same itemid and description

(4,'ITEMID1','ITEMDECRIPTION',400, 1)

This allows you to match rows on you PK for deletes and updates.

Nick-272488 SSC Rookie Points: 42 More actions · Answer 5

my pk in the specific table will be id,code

and will never be updated.so i will not search by description

but by code.

I know that this is not the best solution but now i have to do this.

gbn SSCertifiable Points: 6429 More actions · Answer 6

So associate your rows on column "code". No need for triggers...

UPDATE Table SET DESCR = (New Value) WHERE Code = (Unchanging Value)