Sql Updating Data

[mupp3t]

Good Afternoon All

I'm new to sql programming and I've been asked to create a trigger to move data along a row. Trigger Created As Follows:-

create trigger movedata
on Testing
for update
as
If update (col_1)
insert into testing(col_2)
select col_1 from
testing

However everytime I update col_1, the inserted data is added to an extra row not the same row.

Is this possible or am I living in a dream world!!!!

Thanks
M

 

[SQLSister]

Since you wrote an insert statement of course it is added to a new row. Of even more concern, it would add all the rows in the table not just the ones that were updated. YOu want to use the inserted psuedotable inthe select part of your statement.
If you want to add the same information to col2 that is col1, then write an update statment. But why would you want two columns with the same information? Also if it ijust a simple copy of the same information or calculation based on col1, it would be more efficient to do this in a constraint.


Questions about posting. See faq183-874

 

[Katy44]

Do you want to move existing data? Or move data as it's inserted?

 

[mupp3t]

Firstly

sqlsister, thanks for your reply, sorry I'm a Network Engineer not a programmer, its just the SQL Help isn't that good.

I put an insert statement in, because I wanted to insert whats in col_1 to col_2 before col_1 is updated, then continue this a few times.

I take it, its not that easy to just type before update move data here to there.

But if its easier to do a constraint, I'll look into that.

Thanks again

 

[SQLBill]

You need to understand the difference between UPDATE and INSERT, so here goes:

I have a table with three columns. The table has one row of data.

Row# COL1 COL2
1 A

Now I run an INSERT command to INSERT value B into COL2:

Row# COL1 COL2
1 A
2 B

Now I run an UPDATE command to UPDATE value C into COL2 where COL1 = A

Row# COL1 COL2
1 A C
2 B

To remember the difference, think the word ROW after each command. INSERT ROW, UPDATE ROW, DELETE ROW.

So when you do an INSERT, you are really INSERTing a ROW. When you do an UPDATE, you are UPDATE(ing) a ROW.

Does that help?

-SQLBill