Need help using DB triggers

Classic Servoy
1

Good afternoon from Gran Canaria.

I need some help using DB triggers, maybe I´m not using the right way to get this point:

Think about an invoice table that has a field that contains the invoice number. That number is created based on some fields in the invoice table, like invoice book, invoice year, etc. and a counter based on that fields.

As the fields is created from some fields in that table I cannot use the AutoEnter and I have to generate the invoice number when the record is first saved on the table.

Thinking on that I created a DB trigger onRecordInsert that checks in a counter table for the string “YYYYBB” where YYYY is the invoice year and BB is the book number the invoice belongs to. If the record does not exist it is created and counter is set to 1 and if it exists 1 is added to the counter, the counter record is saved and the string with the number generated like “YYYYBBOOOOOO” (year, book and order in the counter).

The big problem is that the counter table is never updated. Here is some code that mimics what I´m explaining (I did not put the invoice one because is too large), but this does not work either.

/** @type {JSFoundset<db:/gsdespachos/contadores>}*/
var fsCounters=databaseManager.getFoundSet('gsdespachos','contadores');
if(fsCounters.find()){
	fsCounters.nombre='SUCURSALES';
	if(fsCounters.search()){
		fsCounters.contador=fsCounters.contador+1;
	}else{
		fsCounters.newRecord();
		fsCounters.nombre='SUCURSALES';
		fsCounters.contador=1;
	}
	databaseManager.saveData(fsCounters);
	record.codsucursal=fsCounters.contador;
}

Any clue or advice on how to do this??

Thanks in advance

2

Hi:

Since the event is called the method that calculate the field. This method receive the record object as parameter, modifiy this record and returns with the new values.

In the example, we search the last record before the current. The rest is similar to your process.

Test you all the fields have value in the insert moment, if not, you can launch the event in the modification.

I hope you serve.

function numberDoc(record) {	
	var query="select Id from myTable where year="+record["year"]+" and libro="+record["book"]+" and Id<"+record["Id"]+" order by Id DESC";
	var data=databaseManager.getDataSetByQuery(myServer, query, null, 1);
	if (data.getMaxRowIndex()==0) {		
		record["Id"] = 1;
	}
	else {
		record["Id"] = data.getValue(1,1)+1;
	}
}
3

Thanks Adelo I will try this option, but I guess that this method could be bad for performance when you have thousands of records, have you tried this with many many many records?? How fast is it?

Anyway, the big question is: Can I call a saveData() from a DB trigger?

Just think that I would like to update the stock of a product each time an invoice line is saved, I will search the product change the stock and save the record but the record will not be saved!! unless I be doing somthing wrong in my code.

Could anyone tell me more about this or give me some sample code??

Thanks.

4

Hi all;

An SQL query that returns only 1 indexed record should be immediate.

On the other hand, make a save data from an event, I do not think it works; that is why change the record immediately before save it.

5

Thanks Adelo.

That is no good I thought that I could use DB triggers to update other tables.

6

You can, but note that in your example, you are modifying the same record in the same table, so that behavior varies.