> > > I have some tables that contain records that the user should not
> be
> > > able to modify or delete. Other records in the same table
> should be
> > > freely modified, deleted, whatever. I'm trying to come up with
> > > an 'on update or delete' trigger that will prevent the change if
> a
> > > field value exists.
> > >
> > > if (NEW.SomeField = 0) then
> > > DoNotChangeMe;
> > >
> > > Is something like this possible from within a trigger or is
> there a
> > > better way to protect these records at the server level?
> >
> > You can do this via a trigger that raises an exception...
> >
> > Here's an example:
> > CREATE EXCEPTION DELETE_NOT_ALLOWED 'System data - not allowed to
> delete
> > this.';
> >
> >
> > SET TERM ^^ ;
> > CREATE TRIGGER CATEGORIE_SYSTEEM FOR CATEGORIE ACTIVE BEFORE
> DELETE POSITION
> > 0 AS
> > BEGIN
> > if (old.SYSTEEM = 'T')
> > then exception DELETE_NOT_ALLOWED;
> > END
> > ^^
> > SET TERM ; ^^
> >
> > Of course, you DO need to handle the exception :-)
>
> I can see where this would work but I would prefer that the server
> just disallow the deletion. I don't want my client application to
> have to handle the exception; I just want the record to not be
> deleted. Is there another way?

Define "just disallow" --

* raise an exception?
* ignore the DELETE?

How would this work, what would be your feedback?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com