Sql Triggers with some examples

Table creation:

create table general_comments (
 comment_id  integer primary key,
 on_what_id  integer not null,
 on_which_table  varchar(50),
 user_id   not null references users,
 comment_date  date not null,
 ip_address  varchar(50) not null,
 modified_date date not null,
 content   clob,
-- is the content in HTML or plain text (the default)
 html_p   char(1) default 'f' check(html_p in ('t','f')),
 approved_p  char(1) default 't' check(approved_p in ('t','f'))
);


Trigger Creation:

create trigger general_comments_modified
before insert or update on general_comments
for each row
begin
 :new.modified_date := sysdate;
end;



Another One Type Of Trigger:


create table queries (
 query_id integer primary key,
 query_name varchar(100) not null,
 query_owner not null references users,
 definition_time date not null,
 -- if this is non-null,
 we just forget about all the query_columns
 -- stuff; the user has hand edited the SQL
 query_sql varchar(4000)
);

create table queries_audit (
 query_id integer not null,
 audit_time date not null,
 query_sql varchar(4000)
);



create or replace trigger queries_audit_sql
before update on queries
for each row
when (old.query_sql is not null and (new.query_sql is null
or old.query_sql <> new.query_sql))
begin
insert into queries_audit (query_id, audit_time, query_sql)
values
(:old.query_id, sysdate, :old.query_sql);
end;