Как предотвратить удаление первой строки в таблице (PostgreSQL)?
- вместо «selected» укажите «value: params [: folder]»
- Я думаю, что более элегантное решение в контроллере, скажем, у вас есть модель «File». Поскольку значение может быть изменено и выбранное будет иметь неправильное значение
def new
@file = File.new
@file.folder = params[:folder]
end
5 ответов
The best way I see to accomplish this is by creating a delete trigger on this table. Basically, you'll have to write a stored procedure to make sure that this 'default' category will always exist, and then enforce it using a trigger ON DELETE event on this table. A good way to do this is create a per-row trigger that will guarantee that on DELETE events the 'default' category row will never be deleted.
Please check out PostgreSQL's documentation about triggers and stored procedures:
http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html
http://www.postgresql.org/docs/8.3/interactive/plpgsql.html
There's also valuable examples in this wiki:
http://wiki.postgresql.org/wiki/A_Brief_Real-world_Trigger_Example
Вы были правы, думая о системе правил. Здесь - ссылка на пример, соответствующий вашей проблеме. Это даже проще, чем триггеры:
create rule protect_first_entry_update as
on update to your_table
where old.id = your_id
do instead nothing;
create rule protect_first_entry_delete as
on delete to your_table
where old.id = your_id
do instead nothing;
В некоторых ответах пропущен один момент: также необходимо ограничить обновление защищенной строки. В противном случае можно сначала обновить защищенную строку, чтобы она больше не удовлетворяла критерию запрещенного удаления, а затем можно удалить обновленную строку, поскольку она больше не защищена.
You want to define a BEFORE DELETE trigger on the table. When you attempt to delete the row (either match by PK or have a separate "protect" boolean column), RAISE an exception.
I'm not familiar with PostgreSQL syntax, but it looks like this is how you'd do it:
CREATE FUNCTION check_del_cat() RETURNS trigger AS $check_del_cat$
BEGIN
IF OLD.ID = 1 /*substitute primary key value for your row*/ THEN
RAISE EXCEPTION 'cannot delete default category';
END IF;
END;
$check_del_cat$ LANGUAGE plpgsql;
CREATE TRIGGER check_del_cat BEFORE DELETE ON categories /*table name*/
FOR EACH ROW EXECUTE PROCEDURE check_del_cat();
В другой таблице (называемой по умолчанию) может быть строка, ссылающаяся на категорию по умолчанию. Ограничение FK не позволит удалить категорию по умолчанию.
Keep in mind how triggers work. They will fire off for every row your delete statement will delete. This doesn't mean you shouldn't use triggers just keep this in mind and most importantly test your usage scenarios and make sure performance meets the requirements.
Should I use a rule or a trigger?
From the official docs: "For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right."
See the docs for details.
http://www.postgresql.org/docs/8.3/interactive/rules-triggers.html