Hi everybody,
i have a view, which contains some columns of two tables. I would like to insert informtions to this view but since this view is created through a join, it is impossible to insert informations to it.
So i decided to use an instead of trigger to do that but i don't know how?
CREATE TABLE Person (
PersonID integer NOT NULL,
Lastname varchar(255) NOT NULL,
Firstname varchar(255) NOT NULL,
Title char(5) NOT NULL WITH DEFAULT 'Dr.',
PRIMARY KEY (PersonID)
)
CREATE TABLE Adress(
PersonID integer NOT NULL,
City varchar(255) NOT NULL,
Postcode char(5) NOT NULL,
Street varchar(255) NOT NULL,
Number integer NOT NULL,
Country varchar(255) NOT NULL WITH DEFAULT 'CANADA',
FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
ON DELETE CASCADE
)
CREATE VIEW PersonView (personid, lastname, firstname, city, Country) AS
SELECT p.personid, p.lastname, p.firstname, a.city, a.country
FROM person AS p
INNER JOIN adress AS a
ON p.personid=a.personid
CREATE TRIGGER PersonTrig instead of insert on PersonView referencing new as n for each row mode db2sql
BEGIN ATOMIC
Insert into person(lastname,firstname) values(n.lastname,n.firstname);
-- How should the insert into adress look like ( i don't know which personid is
-- created in the first part
END
------------------------------------------------------
In the end, the following command should insert data in the view:
insert into PersonView(lastname,firstname,city,country) values('Smith','Ron','London','England');
Could anyone help me please?
thanks in advance.
Best Regards,
Maryan