Thanks for the quick reply
What I've done thus far
CREATE TABLE acct_type(
acct_type char(1) check(acct_type IN ('b','i','r') primary key not null);
*b represents balance sheet accounts, i represents income statement accounts and r represents retained account*
CREATE TABLE acct(
acct_num char(4) not null primary key,
acct_name text not null,
acct_type char(1) references acct_type);
*acct table for balance sheet accounts*
CREATE TABLE dept(
dept_num char(5) not null primary key,
dept_name text not null);
For income statement accounts trying to do this
CREATE TABLE accdept(
acct_dept_num char(9) *this where I'm stuck, I want the last 5 characters to refer to the dept_num of the dept table*
acct_dept_name text not null,
acct_type char(1) references acct_type
*accdept table to be used for income statement accounts*
hope this info is helpful
Kooter12000
try this
1. first create a function
create or replace function f() returns trigger as $$
declare
r record;
begin
select * into r from dept where dept_num =substring(NEW.i from 5 for 5);
if not found then
raise info 'constraint error';
return NULL;
else
raise info 'constraint fulfill';
return NEW;
end if;
end;
$$ language plpgsql
next create a trigger
create trigger t_acdept before insert on accdept for each row execute procedure f();
it's late but i think it should work.
let me know if it is helpfull.
8
