include$(SRCDIR)/Makefile.global

CONTRIBDIR=$(LIBDIR)/modules

CFLAGS+=$(CFLAGS_SL) -I$(SRCDIR)/include

CFLAGS+= -DREFINT_VERBOSE

TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql\

autoinc$(DLSUFFIX) autoinc.sql moddatetime$(DLSUFFIX) moddatetime.sql\

insert_username$(DLSUFFIX) insert_username.sql

TARGETS= refint$(DLSUFFIX) refint.sql

CLEANFILES+=$(TARGETS)

all::$(TARGETS)

install:: all$(CONTRIBDIR)

$(INSTALL) -c README$(CONTRIBDIR)/README.spi

forfin*.example*.sql*$(DLSUFFIX);do$(INSTALL) -c$$f$(CONTRIBDIR)/$$f;done

$(CONTRIBDIR):

mkdir -p$(CONTRIBDIR)

%.sql:%.source

rm -f$@;\

C=`pwd`;\

sed -e"s:_OBJWD_:$(CONTRIBDIR):g"\

sed -e"s:_OBJWD_:$$C:g"\

-e"s:_DLSUFFIX_:$(DLSUFFIX):g"<$<>$@

clean:

rm -f$(TARGETS)*.o

rm -f$(TARGETS)

check_primary_key () is to used for foreign keys of a table.

Youhave to create trigger (BEFORE INSERT OR UPDATE) using this

function on a table referencing another table. Youhave to specify

Youare to create trigger (BEFORE INSERT OR UPDATE) using this

function on a table referencing another table. Youare to specify

as function arguments: triggered table column names which correspond

to foreign key, referenced table name and column names in referenced

table which correspond to primary/unique key.

check_foreign_key () is to used for primary/unique keys of a table.

Youhave to create trigger (BEFORE DELETE OR UPDATE) using this

function on a table referenced by another table(s). Youhave to specify

Youare to create trigger (BEFORE DELETE OR UPDATE) using this

function on a table referenced by another table(s). Youare to specify

as function arguments: number of references for which function has to

performe checking, action if referencing key found ('cascade' - to delete

corresponding foreign key, 'restrict' - to abort transaction if foreign keys

Old internally supported time-travel (TT) used insert/delete

transaction commit times. To get the same feature using triggers

youhave to add to a table two columns of abstime type to store

youare to add to a table two columns of abstime type to store

date when a tuple was inserted (start_date) and changed/deleted

(stop_date):

CREATE TABLE XXX (

......

date_onabstime,

date_offabstime

date_onabstime default currabstime(),

date_offabstime default 'infinity'

......

);

CREATE TRIGGER timetravel

BEFORE INSERT OR DELETE OR UPDATE ON tttest

FOR EACH ROW

EXECUTE PROCEDURE

timetravel (date_on, date_off);

Tuples being inserted with NULLs in date_on/date_off will get current

date in date_on (name of start_date column in XXX) and INFINITY in date_off

(name of stop_date column in XXX).

- so, tuples being inserted with NULLs in date_on/date_off will get

_current_date_ in date_on (name of start_date column in XXX) and INFINITY in

date_off (name of stop_date column in XXX).

Tuples with stop_date equal INFINITY are "valid now": when trigger will

be fired for UPDATE/DELETE of a tuple with stop_date NOT equal INFINITY then

(and with the same data in other columns as in tuple being deleted).

NOTE:

1. To get tuples "valid now" youhave to add _stop_date_ = 'infinity'

1. To get tuples "valid now" youare to add _stop_date_ = 'infinity'

to WHERE. Internally supported TT allowed to avoid this...

Fixed rewriting RULEs could help here...

As work arround you may use VIEWs...

timetravel() is general trigger function.

You have to create trigger BEFORE (!!!) INSERT OR UPDATE OR DELETE using

this function on a time-traveled table. You have to specify two arguments:

name of start_date column and name of stop_date column in triggered table.

You are to create trigger BEFORE (!!!) UPDATE OR DELETE using this

function on a time-traveled table. You are to specify two arguments: name of

start_date column and name of stop_date column in triggered table.

currabstime() may be used in DEFAULT for start_date column to get

current date.

set_timetravel() allows you turn time-travel ON/OFF for a table:

set_timetravel('XXX', 1) will turn TT ON for table XXX (and report

old status).

set_timetravel('XXX', 0) will turn TT OFF for table XXX (-"-).

Turning TT OFF allows you do with a table ALL what you want!

Turning TT OFF allows you do with a table ALL what you want.

There is example in timetravel.example.

To CREATE FUNCTIONs use timetravel.sql (will be made by gmake from

timetravel.source).

3. autoinc.c - function for implementing AUTOINCREMENT/IDENTITY feature.

You have to create BEFORE INSERT OR UPDATE trigger using function

autoinc(). You have to specify as function arguments: column name

(of int4 type) for which you want to get this feature and name of

SEQUENCE from which next value has to be fetched when NULL or 0

value is being inserted into column (, ... - you are able to specify

as many column/sequence pairs as you need).

There is example in autoinc.example.

To CREATE FUNCTION use autoinc.sql (will be made by gmake from

autoinc.source).

4. insert_username.c - function for inserting user names.

You have to create BEFORE INSERT OR UPDATE trigger using the function

insert_username(). You have to specify as a function argument: the column

name (of text type) in which user names will be inserted. Note that user

names will be inserted irregardless of the initial value of the field, so

that users cannot bypass this functionality by simply defining the field to

be NOT NULL.

There is an example in insert_username.example.

To CREATE FUNCTION use insert_username.sql (will be made by gmake from

insert_username.source).

5. moddatetime.c - function for maintaining a modification datetime stamp.

You have to create a BEFORE UPDATE trigger using the function moddatetime().

One argument must be given, that is the name of the field that is of type

datetime that is to be used as the modification time stamp.

There is an example in moddatetime.example.

To CREATE FUNCTION use moddatetime.sql ( will be made by gmake from

moddatetime.source).

CREATETABLEA (

IDint4not null,

id1int4not null,

primary key (ID,ID1)

);

CREATETABLEB (

REFBint4,

REFB1INT4

);

CREATEINDEXBION B (REFB);

CREATETABLEC (

REFCint4,

REFC1int4

);

CREATEINDEXCION C (REFC);

CREATETRIGGERAT BEFOREDELETEON A FOR EACH ROW

EXECUTE PROCEDURE

check_foreign_key (2,'cascade','ID','id1','B','REFB','REFB1','C','REFC','REFC1');

CREATETRIGGERAT1 AFTERUPDATEON A FOR EACH ROW

EXECUTE PROCEDURE

check_foreign_key (2,'cascade','ID','id1','B','REFB','REFB1','C','REFC','REFC1');

CREATETRIGGERBT BEFORE INSERTORUPDATEON B FOR EACH ROW

EXECUTE PROCEDURE

check_primary_key ('REFB','REFB1','A','ID','ID1');

CREATETRIGGERCT BEFORE INSERTORUPDATEON C FOR EACH ROW

EXECUTE PROCEDURE

check_primary_key ('REFC','REFC1','A','ID','ID1');

INSERT INTO AVALUES (10,10);

INSERT INTO AVALUES (20,20);

INSERT INTO AVALUES (30,30);

INSERT INTO AVALUES (40,41);

INSERT INTO AVALUES (50,50);

INSERT INTO BVALUES (1);-- invalid reference

INSERT INTO BVALUES (10,10);

INSERT INTO BVALUES (30,30);

INSERT INTO BVALUES (30,30);

INSERT INTO CVALUES (11);-- invalid reference

INSERT INTO CVALUES (20,20);

INSERT INTO CVALUES (20,21);

INSERT INTO CVALUES (30,30);

update Aset ID=100 , ID1=199where ID=30 ;

SELECT*FROM A;

SELECT*FROM B;

SELECT*FROM C;

Here are general trigger functions provided as workable examples

of using SPI and triggers. "General" means that functions may be

used for defining triggers for any tables but you have to specify

table/field names (as described below) while creating a trigger.

1. refint.c - functions for implementing referential integrity.

check_primary_key () is to used for foreign keys of a table.

You are to create trigger (BEFORE INSERT OR UPDATE) using this

function on a table referencing another table. You are to specify

as function arguments: triggered table column names which correspond

to foreign key, referenced table name and column names in referenced

table which correspond to primary/unique key.

You may create as many triggers as you need - one trigger for

one reference.

check_foreign_key () is to used for primary/unique keys of a table.

You are to create trigger (BEFORE DELETE OR UPDATE) using this

function on a table referenced by another table(s). You are to specify

as function arguments: number of references for which function has to

performe checking, action if referencing key found ('cascade' - to delete

corresponding foreign key, 'restrict' - to abort transaction if foreign keys

exist, 'setnull' - to set foreign key referencing primary/unique key

being deleted to null), triggered table column names which correspond

to primary/unique key, referencing table name and column names corresponding

to foreign key (, ... - as many referencing tables/keys as specified

by first argument).

Note, that NOT NULL constraint and unique index have to be defined by

youself.

There are examples in refint.example and regression tests

(sql/triggers.sql).

To CREATE FUNCTIONs use refint.sql (will be made by gmake from

refint.source).

# Excuse me for my bad english. Massimo Lambertini

#

#

# New check foreign key

#

I think that cascade mode is to be considered like that the operation over

main table is to be made also in referenced table .

When i Delete , i must delete from referenced table ,

but when i update , i update referenced table and not delete like unmodified refint.c .

I made a patch that when i update it check the type of modified key ( if is a text , char() i

added '') and then create a update query that do the right think .

For my point of view that policy is helpfull because i do not have in referenced table

loss of information .

In preprocessor subdir i have placed a little utility that from a SQL92 table definition,

it create all trigger for foreign key .

the schema that i use to analyze the problem is this

create table

A

( key int4 not null primary key ,...) ;

create table

REFERENCED_B

( key int 4 , ... ,

foreign key ( key ) references A --

);

CREATE TABLE

gruppo

(

codice_gruppo int4NOT NULL,

descrizionevarchar(32)NOT NULL

primary key ( codice_gruppo )

) ;

CREATE TABLE

fa_parte

(

codice_gruppo int4NOT NULL,

codice_contatto int4NOT NULL,

primary key ( codice_gruppo,codice_contatto ) ,

foreign key ( codice_gruppo )references gruppo--

);

char*strtoupper(char*string)

{

inti ;

for (i=0;i<strlen(string);i++)

{

string[i]=toupper(string[i]);

}

returnstring;

}

voidmain (charargc ,char**argv )

{

charstr[250];

intsw=0 ;

while (fgets (str,240,stdin) )

{

if (sw==0 )printf("%s",strtoupper(str));

}

}