novice: trigger to update values in a 2nd table without overdoing it

[voxinforma]

I'm trying to write a trigger so that when field1 in table1 is created or updated with a specific value (let's say the string "approved&quot, the value of field2 (an ObjectID) in the corresponding row from table1 will be inserted into table2.

If field2 already exists in table2, I don't want to insert it twice (and it is at this that I have been failing).

When field1 is modified so that it contains a string other than "approved", I need to delete field2 from table2.

Any help would be insanely appreciated.

--voxi

 

[rajeevnandanmishra]

Hi there,
I don't know you code for trigger and the structure of both tables.
Just go through the following snippet.
-----------------------
Your trigger starting ..
.
.
.
declare @string varchar(20), @field2 varchar(20)
select @string=field1, @field2=field2 from inserted
if @string='Approved'
begin
if exists(select a.* from table2 a, inserted b where
a.field2=b.field2)
insert table2 values(...)
select field2, ... from inserted
where not in (select
end
else
begin
delete table2 where field2=@field2
end
------------------

I hope this will move you in right direction

 

[voxinforma]

thanks, i'll give it a try.

--voxi