database trigger instead of auto-increment

jkuiper · Post by **jkuiper** » Wed 04 Feb 2009 16:27

We going to use a trigger instead of auto-increment. How are the MyDAC-componments working with this trigger?

Dimon · Post by **Dimon** » Thu 05 Feb 2009 08:32

A trigger is a database object that is executed automatically when a particular event occurs for a table. MySQL client should not and can not control work of triggers.

jkuiper · Post by **jkuiper** » Thu 05 Feb 2009 08:52

Yes I know. But will the MyDAC components see that the trigger will insert a new ID-number and send it back to my application or do I have to refresh the record. With an auto-increment it goes automatically.

jkuiper · Post by **jkuiper** » Thu 05 Feb 2009 15:50

So, I created a trigger on a table

Code: Select all

CREATE 
	DEFINER = 'john'@'%'
TRIGGER test.trigger1
	BEFORE INSERT
	ON test.testtable
	FOR EACH ROW
BEGIN
  DECLARE volgnummer int;
  SELECT MAX(ID) 
         FROM testtable 
         INTO volgnummer;
  SET NEW.ID = volgnummer + 1;
END

It's working. But there's a disadvantage. It's not returning the new key value and you have to use myquery.refresh to see the new renerated values.
Myquery.refreshrecord doesn't work. The component looks to a refference with the value NULL, but doesn't exists (see log from dbmonitor) and Delphi tells: "refresh failed. Found 0 records"

Is this the only way to accomplish new records into the dataset.

Code: Select all

log DBMonitor
-----------------
C:\Users\john\Documents\RAD Studio\Projects\triggertest\Project5.exe
  05-02-2009 16:44:24 n/a MyDAC monitoring is started. Complete
  05-02-2009 16:44:24 0:0.141 Connect: john@localhost Complete
  05-02-2009 16:44:25 0:0.0 SQL Execute: select * from testtable
 Complete
  05-02-2009 16:44:44 0:0.31 SQL Execute: INSERT INTO testtable
  (name, telephone)
VALUES
  (?, ?)

:name(String[13],IN)='refreshrecord' 
:telephone(String[7],IN)='3433444' Complete
  05-02-2009 16:44:44 0:0.0 SQL Execute: select * from testtable
WHERE 
  testtable.ID IS NULL 

 Complete

Dimon · Post by **Dimon** » Fri 06 Feb 2009 08:49

The point is that MySQL server doesn't return any information about new values that are set in a trigger, as distinct from autoincrement fields. Therefore you can't get these values in other ways than executing refresh.

jkuiper · Post by **jkuiper** » Fri 06 Feb 2009 14:20

Oke. Now there's a property in myquery.options.returnparams. This is what help says:

Syntax
property ReturnParams: boolean;

Remarks
Use the ReturnParams property to return the new value of the fields to dataset after insert or update. Actual value of field after insert or update may be different from the value stored in local memory if the table has a trigger. When ReturnParams is True, OUT parameters of SQLInsert and SQLUpdate statements is assigned to corresponding fields. The default value is False.

I don't use SQLInsert, but I know when record will be posted, MyQuery creates internally SQL statements. Are these SQL's using SQLInsert? If it is, it should return a new value. As far as I know the trigger will be executed before the actual insert goes to the table.

Dimon · Post by **Dimon** » Mon 09 Feb 2009 12:12

The problem is that triggers in MySQL do not return parameters.