Using trigger to update additional fields
-
Hi.
I'm trying to update some fields in my table using a Trigger for insert. But i'm having problems to update the exact record that is being added to my table.
this is a sample of my idea:
CREATE TRIGGER trgtest ON testtable
FOR INSERT
AS
[variable declaration]
@field1.. @field4 varchar
@timestamp timestamp
declare mycursor cursor for
select field1, field2, field3, timestamp
from inserted
open mycursor
fetch next mycursor into @field1, @field2, @field3, @timestamp
while @@fetch_status = 0
begin
-- get additional data
select @field4 = field4 from othertable o
where o.field1 = @field1 and o.field2 = @field2
-- update current row. using timestamp
update testtable set field4 = @field4 where timestamp = @timestamp
if @@rowcount = 0
ERROR!!!
fetch next mycursor into @field1, @field2, @field3, @timestamp
end
close mycursor...
...
...
i'm trying to match timestamp field with @timestamp variable, i guess they must match. But i'm getting into the ERROR!!!. Why??.
i've tried to declare mycursor for UPDATE but i get the error : "CURSOR is read only" when i try to update using "where current of mycursor"
anyway, i want to know what is the best way to get this right.. if you have some sample script, i will appreciate.
Thank you...
Victor
P.S. In Oracle this is as simple as say: ":new.field4 := varfield4"
Sorry for my english
.. i hope you understand!!... -
You should be able to do this as a set-based operation without cursors or local variable declarations:
Update testtable
Set field4 = o.field4
From testtable As t
Inner Join inserted as new
On ( <join up keys here > )
Inner Join othertable As o
On (new.field1 = o.field1 And
new.field2 = o.field2 )
-
I'm not familiar with the nuts and bolts of how an update is actually accomplished by SQL Server, but you are trying to update a record which has not yet been fully inserted. That can't be good.
If you want to insert data from othertable, do it in the original INSERT statement, or wait until the insert is completed.
-
Hi PW...
i think this update is ok, and work...but, i have one more question...
About the use of a timestamp field as the update join key between testtable and inserted. look:
Update testtable
Set field4 = o.field4
From testtable As t
Inner Join inserted as new
On ( t.timestamp = new.timestamp 
Inner Join othertable As o
On (new.field1 = o.field1 And
new.field2 = o.field2 )
this "t.timestamp = new.timestamp " is valid?... i'm tryin this but i can't get to update field4. Maybe you have an explanation about the behavior of this timestamp in this cases...
Thank you!!
Vic
-
The update works fine when I tested it. Are you sure you have matching field1 and field2 in [othertable], if not then not update will be made
Far away is close at hand in the images of elsewhere.
Anon. -
>>Maybe you have an explanation about the behavior of this timestamp in this cases...
I have no idea. If you go back to your original post, we didn't get DDL for the tables, hence we have no idea what datatype "timestamp" is. I simply included it in my where clause because in your intial code posting you indicated it was the join field.
-
if you are using the SQL Server built-in timestamp datatype that join will never work because every time a row is created or affected in any way it will change. Note that timestamp is actually binary (auwful missnomer from MS
)there for
t.timestamp = new.timestamp is not going to match, that should explain why you get an error
* Noel -
I beg to differ, this an insert trigger, the timestamp in the inserted table will be the same as the row inserted in the actual table.
Far away is close at hand in the images of elsewhere.
Anon. -
>>I beg to differ, this an insert trigger, the timestamp in the inserted table will be the same as the row inserted in the actual table<<
OOPS!! I was thinking too much on the update. Guess I need more coffe
sorry
* Noel -
No need to apologize Noel
Besides, made me look again to make sure though
More Coffee the better
Far away is close at hand in the images of elsewhere.
Anon. -
Hello again:
Well, i made some test using timestamp field as key for update the same table and it works!!.. even declaring a cursor (in case of an insert select). and works. So, there is no doubt about that the use of timestamp is valid...
anyway i wanted to confirm or validate mi concepts with you. thanks again..
i still have the error in my original (bigger) code.. but i'm sure that's because i need more cofee too!!!!..
thanks again...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply