| Subject | Re: [firebird-support] Maintaining count with trigger, prevent updating on cascades |
|---|---|
| Author | Michael Ludwig |
| Post date | 2010-07-15T08:20:59Z |
sqlsvr schrieb am 15.07.2010 um 01:46 (-0000):
that you're using the usual technique of inserting +1 or -1 and then
have the occasional SUM() query consolidate the count, there shouldn't
be much to worry about.
Alternatively, you could do more explicit hand-coding by denying
explicit DML operations and using stored procedures to handle blog
post and blog addition and removal with the respective updating of
your counters. But it sounds like more code wanting special attention,
so I probably wouldn't go there.
You could also check some condition in your trigger to detect the case
of blog removal where there is no need to update any counters as there
will be nothing left to count. The trigger will still execute, but not
perform the INSERT operation. Just can't think of how exactly to code
that right now. You would need to set some state in the blog removal
operation, and then check for that case in the counter trigger.
/* in your counter update trigger */
IF (NOT :BLOG_BEING_REMOVED) THEN /* update the counter */
Don't know if you can do something like that, and not sure it's worth
the hassle for updating a counter and you really need that sort of
fine-grained control here, but I would be interested as well to know if
such logic involving inter-procedure state is possible, and how you
would go about implementing it in Firebird. Looks like you'd need to
set a global variable.
I trust others will have more expert advice to offer here.
--
Michael Ludwig
> I have two tables: blogs and blog posts. Blogs keep a count of blogI'm also assuming it will cause the trigger to execute, but provided
> posts and blog posts has a foreign key (cascade) to blogs. There is a
> trigger on blog posts that updates the count in blogs table.
>
> Now, if a blog post is inserted/deleted, I want to update the blogs
> count. However, if a blog is deleted....it will cascade the delete on
> the blog posts table.
>
> I'm assuming that this will cause the trigger to execute and update
> the blogs table unnecessarily? If so, is there anyway to stop it?
that you're using the usual technique of inserting +1 or -1 and then
have the occasional SUM() query consolidate the count, there shouldn't
be much to worry about.
Alternatively, you could do more explicit hand-coding by denying
explicit DML operations and using stored procedures to handle blog
post and blog addition and removal with the respective updating of
your counters. But it sounds like more code wanting special attention,
so I probably wouldn't go there.
You could also check some condition in your trigger to detect the case
of blog removal where there is no need to update any counters as there
will be nothing left to count. The trigger will still execute, but not
perform the INSERT operation. Just can't think of how exactly to code
that right now. You would need to set some state in the blog removal
operation, and then check for that case in the counter trigger.
/* in your counter update trigger */
IF (NOT :BLOG_BEING_REMOVED) THEN /* update the counter */
Don't know if you can do something like that, and not sure it's worth
the hassle for updating a counter and you really need that sort of
fine-grained control here, but I would be interested as well to know if
such logic involving inter-procedure state is possible, and how you
would go about implementing it in Firebird. Looks like you'd need to
set a global variable.
I trust others will have more expert advice to offer here.
--
Michael Ludwig