Error with create trigger command

[Former Member]

Hello Everybody

I want to create some database triggers for logging deletes

CREATE TABLE "BWALLE"."STDELETELOG"

(

"UHRZEIT" Timestamp NOT NULL DEFAULT TIMESTAMP,

"BENUTZER" Char (40) ASCII NOT NULL DEFAULT USER,

"TABELLE" Char (30) ASCII NOT NULL DEFAULT ' ',

"TABELLENKEY" Char (30) ASCII NOT NULL DEFAULT ' ',

"KEYWERT" Char (30) ASCII NOT NULL DEFAULT ' '

)

This one is ok and works as expected !!!!

CREATE TRIGGER STPERSONALSTAMMDELETE FOR BWALLE.STPERSONALSTAMM AFTER DELETE EXECUTE (

INSERT INTO BWALLE.STDELETELOG (UHRZEIT,BENUTZER,TABELLE,TABELLENKEY,KEYWERT)

VALUES (DEFAULT, DEFAULT,'STPERSONALSTAMM','STPERSONALSTAMMID', :OLD.STPERSONALSTAMMID);

)

This returns and error ????

CREATE TRIGGER AUTONUMDELETE FOR BWALLE.AUTONUM AFTER

DELETE EXECUTE (

INSERT INTO BWALLE.STDELETELOG (UHRZEIT,BENUTZER,TABELLE,TABELLENKEY,KEYWERT)

VALUES (DEFAULT, DEFAULT,'AUTONUM',:OLD.SUCHBEGRIFF, CHR(:OLD.WERT));

)

General error;-7035 POS(161) DEFAULT specification not allowed for this column

The first default is marked red ???

Any help welcomed

Best regards

Albert

[lbreddemann · Accepted solution]

HI Albert,

please post the DDL for BWALLE.AUTONUM and BWALLE.STPERSONALSTAMM.

KR Lars

[Former Member · Accepted solution]

Hello Lars

CREATE TABLE "BWALLE"."AUTONUM"

(

"SUCHBEGRIFF" Char (30) ASCII NOT NULL,

"WERT" Fixed (10,0) NOT NULL DEFAULT 0,

"MINIMUM" Fixed (10,0) NOT NULL DEFAULT 0,

"MAXIMUM" Fixed (10,0) NOT NULL DEFAULT 0,

"TRANSFERDATUM" Timestamp DEFAULT TIMESTAMP,

"ANGELEGT" Timestamp DEFAULT TIMESTAMP,

"GEAENDERT" Timestamp DEFAULT TIMESTAMP,

"A_BENUTZER" Char (20) ASCII NOT NULL DEFAULT ' ',

"G_BENUTZER" Char (20) ASCII NOT NULL DEFAULT ' ',

PRIMARY KEY ("SUCHBEGRIFF")

)

CREATE TABLE "BWALLE"."STPERSONALSTAMM"

(

"STPERSONALSTAMMID" Char (8) ASCII NOT NULL,

"STNLSTAMMID" Char (10) ASCII NOT NULL DEFAULT ' ',

"STNAME" Char (40) ASCII NOT NULL DEFAULT ' ',

"STANSCHRIFTZUSATZ" Char (40) ASCII NOT NULL DEFAULT ' ',

"STNAMENSZUSATZ" Char (40) ASCII NOT NULL DEFAULT ' ',

"STVORNAME" Char (40) ASCII NOT NULL DEFAULT ' ',

"STGEBURTSDATUM" Date NOT NULL DEFAULT '0001-01-01',

"STGEBURTSNAME" Char (40) ASCII NOT NULL DEFAULT ' ',

"STORT" Char (40) ASCII NOT NULL DEFAULT ' ',

"STPLZ" Char (10) ASCII NOT NULL DEFAULT ' ',

"STSTRASSE" Char (60) ASCII NOT NULL DEFAULT ' ',

"STTELEFON" Char (14) ASCII NOT NULL DEFAULT ' ',

"STAUSTRITT" Date NOT NULL DEFAULT '9999-12-31',

"STEINTRITTAV" Date NOT NULL DEFAULT '0001-01-01',

"STEINTRITTNL" Date NOT NULL DEFAULT '0001-01-01',

"STSTEUERPFLICHT" Char (2) ASCII NOT NULL DEFAULT ' ',

"STVERSICHERUNGSPFLICHT" Char (4) ASCII NOT NULL DEFAULT ' ',

"STWOCHENARBEITSZEIT" Fixed (11,2) NOT NULL DEFAULT 0.00,

"STKIRCHENSTEUER" Char (2) ASCII NOT NULL DEFAULT ' ',

"STKRANKENKASSE" Char (30) ASCII NOT NULL DEFAULT ' ',

"STSTAATSANGEHOERIGKEIT" Char (16) ASCII NOT NULL DEFAULT ' ',

"STKONTONUMMER" Char (10) ASCII NOT NULL DEFAULT ' ',

"STBLZ" Char (8) ASCII NOT NULL DEFAULT ' ',

"STKINDERANZAHL" Fixed (4,2) NOT NULL DEFAULT 0.00,

"STPERSONENGRUPPE" Char (3) ASCII NOT NULL DEFAULT ' ',

"STTAETIGKEIT" Char (5) ASCII NOT NULL DEFAULT ' ',

"STVERSICHERUNGSNUMMER" Char (12) ASCII NOT NULL DEFAULT ' ',

"STARBEITSVERHAELTNIS" Char (2) ASCII NOT NULL DEFAULT ' ',

"STTARIFSCHLUESSEL" Char (2) ASCII NOT NULL DEFAULT ' ',

"STTARIFGEBIET" Char (2) ASCII NOT NULL DEFAULT ' ',

"STTARIFBEZEICHNUNG" Char (20) ASCII NOT NULL DEFAULT ' ',

"STTARIFGRUPPE" Char (8) ASCII NOT NULL DEFAULT ' ',

"STTARIFSTUFE" Char (2) ASCII NOT NULL DEFAULT ' ',

"STTAGLOHN" Fixed (11,2) NOT NULL DEFAULT 0.00,

"STNACHTLOHN" Fixed (11,2) NOT NULL DEFAULT 0.00,

"STABRECHNUNGSKREIS" Char (2) ASCII NOT NULL DEFAULT ' ',

"STPERSONALTEILBER" Char (4) ASCII NOT NULL DEFAULT ' ',

"STPRIVATNUTZUNGKFZ" Fixed (1,0) NOT NULL DEFAULT 0,

"ANGELEGT" Timestamp NOT NULL DEFAULT '9999-12-31 00:00:00.000000',

"GEAENDERT" Timestamp NOT NULL DEFAULT '9999-12-31 00:00:00.000000',

"GELOESCHT" Timestamp NOT NULL DEFAULT '9999-12-31 00:00:00.000000',

"A_BENUTZER" Char (20) ASCII NOT NULL DEFAULT ' ',

"G_BENUTZER" Char (20) ASCII NOT NULL DEFAULT ' ',

"L_BENUTZER" Char (20) ASCII NOT NULL DEFAULT ' ',

"STTITEL" Char (80) ASCII NOT NULL DEFAULT ' ',

"STFUNKTION1" Char (80) ASCII NOT NULL DEFAULT ' ',

"STFUNKTION2" Char (80) ASCII NOT NULL DEFAULT ' ',

"STFUNKTION3" Char (80) ASCII NOT NULL DEFAULT ' ',

"STORGATYP1" Char (80) ASCII NOT NULL DEFAULT ' ',

"STORGATYP2" Char (80) ASCII NOT NULL DEFAULT ' ',

"STTELEFONFIRMA" Char (30) ASCII NOT NULL DEFAULT ' ',

"STTELEFONMOBIL" Char (30) ASCII NOT NULL DEFAULT ' ',

"STFAXFIRMA" Char (30) ASCII NOT NULL DEFAULT ' ',

"STEMAILFIRMA" Char (80) ASCII NOT NULL DEFAULT ' ',

"STFIRMENTEXTID" Char (10) ASCII NOT NULL DEFAULT ' ',

"STPRIVATNUTZUNGWA" Fixed (4,0) NOT NULL DEFAULT 0,

"STNUTZUNGSVEREINBARUNG" Fixed (1,0) NOT NULL DEFAULT 0,

"LOGINUSER" Char (30) ASCII NOT NULL DEFAULT ' ',

"STNLSTAMMID_2" Char (10) ASCII NOT NULL DEFAULT ' ',

"STHANDYBUDGET" Fixed (13,2) NOT NULL DEFAULT 0.00,

"STHANDYKENNUNG1" Char (30) ASCII NOT NULL DEFAULT ' ',

"STHANDYKENNUNG2" Char (30) ASCII NOT NULL DEFAULT ' ',

"STHANDYKENNUNG3" Char (30) ASCII NOT NULL DEFAULT ' ',

"STFUNKTELEFONKENNUNG" Char (30) ASCII NOT NULL DEFAULT ' ',

"STAUTOTELEFONKENNUNG" Char (30) ASCII NOT NULL DEFAULT ' ',

PRIMARY KEY ("STPERSONALSTAMMID")

)

[lbreddemann · Accepted solution]

Hi Albert,

the problem is not the DEFAULT specification - that error message is wrong here.

The problem is the CHR(:OLD.WERT) value you try to use for the insert.

You've to perform the type conversion before you use the data in the insert statement:

CREATE TRIGGER AUTONUMDELETE FOR BWALLE.AUTONUM AFTER 
DELETE EXECUTE ( 
VAR chr_wert VARCHAR(30);

SET chr_wert = chr(old.wert);

INSERT INTO LARS.STDELETELOG (UHRZEIT,BENUTZER,TABELLE,TABELLENKEY,KEYWERT) 
VALUES (DEFAULT, DEFAULT,'AUTONUM',:OLD.SUCHBEGRIFF,:chr_wert);
)

But this is not the biggest concern here.

It's not such a good idea to use just one logging table for all tables to be logged.

As long as one delete-transaction is running on a "monitored" table and does not commit the delete, the insert into the logtable won't be committed as well.

Thus locks will also be held on the logging table - this may lead to serialization of otherwise indepedendent transactions.

KR Lars

[Former Member · Accepted solution]

Hello Lars

Thank you for your help!

Everthing works as expected.

I only want to monitor deletes for 2-3 tables which should never be deleted !

No appication should fire a delete command for these tables.

Only programmers making mistakes with sql-studio for example or some nightjob with serios logic errors should create a record in the log table.

With this technic we found the solution for our losing records in stpersonalstamm problem.

A administrator started an SAP Testsystem (a copy of our produktion system with old data). On this maschine there was still an active sysncjob that filled stpersonalstamm on our second nosap database with the contens from the SAP-testsystem system. (actual size of Produktion system - 4000 records). **** happens !!

Best regards

Albert