Trigger schema idea

[Daniel Berger]

Hi,

I noticed that neither db:schema:dump nor db:structure:dump included
triggers in the dump. That got me thinking of a generic way to create
a trigger in a migration. I tried to come up with some syntax that I
thought might be reasonably cross platform. What do you think of
something like this?

# Based on example from Oracle 9i, The Complete Reference, p. 513
create_trigger(:name => 'bookshelf_bef_upd_row', :force => true) do |
t|
t.before => 'update'
t.on => 'bookshelf'
t.condition => 'new.rating < old.rating'
t.execute => %Q{
insert into bookshelf_audit
(title, publisher, categoryname, old_rating, new_rating,
audit_date)
values

(:old.title, :old.publisher, :old.category_name, :old.rating, :new.rating,
sysdate)
}
end

create_trigger could take 2 options, the 'name' and a 'force' option.
It would yield a trigger object, which would respond to the following
methods:

# These would accept 'insert', 'update' and 'delete', or any
combination of them
before
after
instead_of

# The table name
on

# The 'when' clause
condition

# The call procedure
execute

How does something like that look? And would it be of any interest to
you?

Regards,

Dan

[Daniel Berger]

BTW, the code above would generate:

create or replace trigger bookshelf_bef_upd_row
before update on bookshelf
for each row
when (new.rating < old.rating)
begin

insert into bookshelf_audit
(title, publisher, categoryname, old_rating, new_rating,
audit_date)
values

(:old.title, :old.publisher, :old.category_name, :old.rating, :new.rating,
sysdate)

end;

[Raimonds Simanovskis]

I think that as trigger syntax is quite Oracle specific there is no
big need to create Ruby DSL around it. You can just include

execute <<-SQL

create or replace trigger bookshelf_bef_upd_row
before update on bookshelf
for each row
when (new.rating < old.rating)
begin
insert into bookshelf_audit
(title, publisher, categoryname, old_rating, new_rating,
audit_date)
values
(:old.title, :old.publisher, :old.category_name, :old.rating, :new.rating,
sysdate)
end;

SQL

in your migration.

Raimonds