Help to create a Trigger

[Armando]

Dear Friends:

I have this source code

Code: Select all  Expand view

    cCmdSql := "CREATE TRIGGER Trg_Cli AFTER INSERT ON 'Clientes' " +;
                        "INSERT INTO Auditoria SET AUD_TAB = 'Clientes';"

    TRY
        oApp:oCon:Execute(cCmdSql)
    CATCH oError
        MsgStop("I Can't create the trigger !",oApp:cAplicacion)
        ShowError(oError)
    END
 

But allways I have the message "I Can't créate the trigger!, why?

BTW, I use MariaDB server

Best regards

[nageswaragunupudi]

CREATE TRIGGER trg_cli AFTER INSERT ON clientes
FOR EACH ROW
BEGIN
INSERT INTO auditoria ( aud_tab ) VALUES ( 'Clientes' );
END

[Armando]

Mr. Rao:

I get this message error

Application
===========
Path and name: C:\AppSap\AppSap.Exe (32 bits)
Size: 4,188,672 bytes
Compiler version: Harbour 3.2.0dev (r1801051438)
FiveWin version: FWH 17.11
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 0 secs
Error occurred at: 26/06/2018, 22:55:09
Error description: (DOS Error -2147352567) WINOLE/1007 [ma-3.0.5][10.3.7-MariaDB]Commands out of sync; you can't run this command now (0x80004005): Microsoft OLE DB Provider for ODBC Drivers
Args:
[ 1] = C CREATE TRIGGER trg_cli AFTER INSERT ON clientes
FOR EACH ROW
BEGIN
INSERT INTO auditoria ( aud_tab ) VALUES ( 'Clientes' );
END


Stack Calls
===========
Called from: => TOLEAUTO:EXECUTE( 0 )
Called from: Source\AppTab.Prg => APPTAB( 117 )
Called from: Source\AppSap.Prg => MAIN( 89 )

System
======
CPU type: Intel(R) Core(TM) i3-6100 CPU @ 3.70GHz 3696 Mhz
Hardware memory: 8089 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Windows total applications running: 5
1 ,
2 , C:\AppSap\AppSap.Exe
3 , C:\WINDOWS\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.17134.112_none_42ecccf
4 DDE Server Window, C:\WINDOWS\System32\OLE32.DLL
5 G, C:\WINDOWS\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.17134.112_none_73d7bfd79f76bee

Variables in use
================
Procedure Type Value
==========================
TOLEAUTO:EXECUTE
Param 1: O Class: ERROR
APPTAB
Param 1: C " CREATE TRIGGER trg_cli AFTER INSERT ON clientes
FOR EACH ROW
BEGIN
INSERT INTO auditoria ( aud_tab ) VALUES ( 'Clientes' );
END
"
Local 1: U
Local 2: U
MAIN
Local 1: C "InnoDB"
Local 2: C ""
Local 3: C " CREATE TRIGGER trg_cli AFTER INSERT ON clientes
FOR EACH ROW
BEGIN
INSERT INTO auditoria ( aud_tab ) VALUES ( 'Clientes' );
END
"
Local 4: U

Linked RDDs
===========
DBF
DBFFPT
DBFBLOB
DBFNTX

DataBases in use
================

Classes in use:
===============
1 ERROR
2 HBCLASS
3 HBOBJECT
4 TAPLICACION
5 TINI
6 WIN_OLEAUTO
7 TOLEAUTO
8 TREG32
9 TSTRUCT

Memory Analysis
===============
382 Static variables

Dynamic memory consume:
Actual Value: 524288 bytes
Highest Value: 524288 bytes

Best regards

[nageswaragunupudi]

This is the trigger I just created on my server and is actually working

Code: Select all  Expand view

  CREATE TRIGGER customer_au AFTER UPDATE ON customer
   FOR EACH ROW
   BEGIN
      INSERT INTO customer_audit ( oldsalary, newsalary )
                  VALUES ( OLD.salary, NEW.salary );
   END

Please try similar syntax. This works both for MySql and MariaDB servers.

Another important thing is that the user must be having privileges to create triggers on the server. If not, the creation fails.

[Armando]

Mr. Rao:

With MySQL server this code Works fine, but with MariaDb do not Works fine.

Code: Select all  Expand view

    TEXT INTO cCmdSql
        CREATE TRIGGER trg_cli AFTER INSERT ON clientes
        FOR EACH ROW
        BEGIN
            INSERT INTO auditoria (aud_tab,aud_cmd) VALUES ('Clientes','El Comando');
        END;
    ENDTEXT

   oApp:oCon:Execute(cCmdSql)
 

Mr. Rao, thank you very much

Regards

[nageswaragunupudi]

I have not tested, but I suggest you remove the last ";" after END and try with MariaDB. This may work keeping in view the type of error you are getting. May I know the result?