laser wrote:
>
>>
>> It's not a bug, just your misunderstanding of how rules work. Rules
>> rewrite queries. What happen in your case is because of the condition
>> your query will be split into two: once with your INSERT with a NOT
>> EXISTS (subquery) and once as an UPDATE with the condition EXISTS
>> (subquery).
>>
>> So the first query will insert with id=1 and then the update sees this
>> row and updates it to 2.
>>
>> What this says is that rules are the wrong tool for what you're trying
>> to do. Conditional rules are powerful but not appropriate here.
>>
>> Have a nice day,
>>
>
> Thanks to clarify, if it's a misunderstanding of rule, then I'll some how
> confuse with DO INSTEAD vs. DO ALSO rule, isn't DO INSTEAD will
> replace original INSERT with the one provided in CREATE RULE?
> ...after some rethinking, can I understand what happened as below step?
>
> 1, INSERT will be rewrite into a INSERT with a EXIST condition clause
> and a UPDATE statement;
> 2, when NOT EXISTS, INSERT succeed, and the query tree in 1 proceed to
> UPDATE;
> 3, the UPDATE saw the INSERT in 2, then UPDATE it;
>
> if so, then I understand what happed there, and it's surly not a bug but
> a mis-use of rule.
It took me a while to get my head around it but essentially the query
tree gets rewritten to
1. execute original statement where not rule.condition
2. execute rule body where rule.condition
In this case, part 1 changes the result of the rule condition for part 2
making both execute. I.E. rule condition is evaluated every time it is
referenced rather than being kept constant for both query tree entries.
To do it in a trigger, you would have to do a before insert trigger that
looks like
update ruleTest set id = id+1 where ruleTest.name = NEW.name;
if rowsaffected > 0 then
return null;
end if;
return new;
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au