Here an examples (sorry the names are only in German)

The scenario: a Liefereant is a Partner. The create table statements:

CREATE TABLE Partner
(
Partner_id INTEGER NOT NULL,
Name VARCHAR(20),
Ort VARCHAR(15),
CONSTRAINT PK_Partner PRIMARY KEY (Partner_id)
);

CREATE TABLE Lieferanten
(
Partner_id INTEGER NOT NULL,
Telefon VARCHAR(30),
Kontaktperson VARCHAR(30),
Rank INTEGER default 3 NOT NULL,
CONSTRAINT PK_Lieferanten PRIMARY KEY (Partner_id),
CONSTRAINT FK_Lieferanten_Partner_id
FOREIGN KEY (Partner_id) REFERENCES Partner (Partner_id),
CONSTRAINT CH_Lieferanten_Rank check (rank between 1 and 5)
);



Then I have created a view with a join, which is not updateable (normally):

create View V_Lieferanten as
select P.Partner_id, P.Name, L.Kontaktperson, P.Ort, L.Telefon from
Lieferanten L
join Partner P on (P.Partner_id = L.Partner_id);


Then I have created a trigger for the insert operation, which at also a row
to the parent table partner:


SET TERM !! ;

CREATE TRIGGER I_Lieferanten FOR V_Lieferanten
BEFORE INSERT AS
BEGIN

insert into Partner (Partner_id, Name, Ort)
values (gen_id(Partner_id, 1), new.Name, new.Ort);

insert into Lieferanten (Partner_id, Telefon, KontaktPerson)
values (gen_id(Partner_id, 0), new.Telefon, new.KontaktPerson);

END !!

Thats all. Now you can insert:

insert into V_Lieferanten (Name, KontaktPerson, Ort, Telefon)
values ('Wal Markt', 'Dr. Noe', 'Duisburg', '040 123 456');

BTW: the create View doesn´t support ordery by statments (was new for me)

Ralf Grenzing (BTW: New born JEDI Knight :-)


iradcarmi schrieb:

> Hi,
>
> In http://www.ibphoenix.com/ibp_act_db.html, Ann Harrison describes a
> Firebird feature - View triggers - which emulates update operations
> on views that cannot be updated naturally, such as views which
> include joins. This sounds like a great feature, but I haven't been
> able to figure out how to use it, or to locate any code samples.
> Can anybody show me the way?
>
> Thanks,
>
> Irad Carmi
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/