Consider the relations R(w, x, y, z) and S(a, b). For each of the
following constraints on R, state which of insert/update/delete trigger(s)
are needed, and give a trigger that enforces the constraint (if
multiple triggers are needed, you only need to give one):

1. Primary key is (w,x)

We need triggers for insert and update, the update trigger is shown
below. Note that on update we have to test whether the key changed,
otherwise updates that leave the key unchanged would be rejected. Also
note that attributes in primary keys must not be NULL (though sqlite
doesn't enforce this rule).

create trigger R_pk_u before update on R
when old.w != new.w or old.x != new.x
begin
	select raise(ABORT, 'Null PK!'
        where new.w is null or new.x is null;
	select raise(ABORT, 'PK violation!')
        from R where new.w = w and new.x = x;
end;

2. y must be unique

This is just like the primary key case, except that NULL value are
allowed. Because NULL never compares equal to anything, not even
itself, it is actually legal to have multiple NULL entries in unique
column.

create trigger R_y_unique_i before insert on R
begin
	select raise(ABORT, 'y is not unique')
        from R where new.y = y;
end;

3. z must not be null

Again, we need insert and update triggers, both behave identically.

create trigger R_z_notnull_i before insert on R
begin
	select raise(ABORT, 'z must not be NULL')
        where new.z is null;
end;

4. wx -> yz *and* y -> x (BCNF violation)

Let's assume we already have a primary key constraint on R. We just
need a way to enforce y -> x.

One way would use S as a helper table: on every insert/update to R,
check whether S is compatible (raise an error if a different x is
already associated with y); if S does not already contain the value
then we insert it for next time. This approach requires insert, update
and delete triggers, and deletion gets tricky because we only want to
remove an entry from S once the last matching tuple in R is
removed. The update case has to worry about both problems:

create trigger R_bcnf_u after update on R
begin
	select raise(ABORT, 'FD violation: y -> x')
        from S where new.y = y and new.x != x;
        insert into S(b,a) select new.x, new.y where not exists (
        	select * from S where b = new.y and a != new.x);
        delete from S where b = new.y and a = new.x and not exists (
        	select * from R where y = new.y and x = new.x);
end;

The other way involves only R, and is far simpler (only insert and
udpate triggers needed):

create trigger R_bcnf_u after udpate on R
begin
	select raise(ABORT, 'FD violation: y -> x')
        from R where new.y = y and new.x != x;
end;

5. y is a foreign key on S(a) (assume S does not change).

This one is simple because S doesn't change:

create trigger R_fk_i before insert on R
begin
	select raise(ABORT, 'Foreign key violation')
        where new.y not in (select a from S);
end;

6. For any given (w,z), no change to R may cause the number of
   matching rows to drop below 3.

We only enforce this when row count decreases, because otherwise there
would be no way to fill up an empty table... we have to check update
and delete cases. Fortunately, the trigger makes it so the only
question is whether the propsed change would leave us with only two
rows (we don't have to worry about the zero-row case):

create trigger R_count_u after update on R
begin
	select raise(ABORT, 'Minimum row count required')
        from R where old.w = w and old.z = z
        group by w,z having count(*) = 2;
end;

7. Changes are not allowed to alter a row's primary key in R.

This one only matters on update.

create trigger R_immutable_pk before update on R
begin
	select raise(ABORT, 'Primary key is immutable')
        where new.w != old.w or new.x != old.x;
end;

8. Challenger: suppose we have the following view:

create view T as select R.*, S.b from R join S on x=a;

... with R.x a foreign key reference to S.a. Define a trigger that
allows an attempted insert on T to create new rows in R and S, as long
as neither primary key exists yet (in which case the attempt to insert
into a view is well-defined).

In other words, given the data below, attempts to insert T(1,2,3,4,5)
or T(3,4,5,6,7) should fail but T(5,6,7,8,9) should succeed and create
new rows R(5,6,7,8) and S(6,9):

R(w,x,y,z)      S(a,b)
===========     =======
1 2 3 4         2 5
1 4 5 6         4 7

The answer here is to create an instead of insert trigger that
intercepts the insert attempt and does the right thing with it. We
assume that appropriate primary key constraints are already in place
on both tables. We apply the FK constraint explicitly, by setting x=a
in our code. If the primary key is already in use on either machine, the corresponding insertion will fail.

create trigger T_insert instead of insert on T
begin
        insert into R values(new.w, new.x, new.y, new.z);
        insert into S values(new.x, new.b);
end;