-1
DELIMITER//
CREATE TRIGGER delete_table_checker 
AFTER DELETE ON transaksi
FOR EACH ROW
DECLARE id_trans VARCHAR(5) unasigned;
DECLARE trans_number VARCHAR(5) unasigned; 
DECLARE target_delete VARCHAR(5) unasigned;
SELECT id_trans:= old.ID_transaksi;
SELECT trans_number:=SUBSTRING(@id_trans,3,3);
DECLARE target_delete VARCHAR(5):=(SELECT CONCAT('TC',@trans_number))
DELETE FROM dbo.table_checker WHERE ID_table_checker=@target_delete
DELIMITER;

It give an error like this

[Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@ID_karyawan VARCHAR(5), @ID_jenis_transaksi VARCHAR(5),@ket_trans VARCHAR(25...' at line 1 [Script position: 2755 - 2915]

I have no idea why it's not working in my SQL, but it working in SQL Server though. Can any one help me what is wrong with my query?

Dale K
  • 25,246
  • 15
  • 42
  • 71
farhan
  • 11
  • 5
  • MySQL or SQL Server? Please only tag one. And there is no way that works in SQL Server. And the error appears to reference code which isn't even shown - are you sure you are showing the correct code? – Dale K Feb 26 '21 at 04:59
  • `id_trans` and `@id_trans` are two different variables (the former is local variable whereas the latter is user-defined variable). Do not use user-defined variable (`@id_trans`) in triggers. – Akina Feb 26 '21 at 05:14

1 Answers1

0

In mariadb/mysql

  1. If there is more than one statement you need to enclose in begin..end
  2. All declared variables have to appear before any other statement
  3. User defined (at) variables and declared variables are not the same thing see How to declare a variable in MySQL?
  4. Use SET to assign values to a variable
  5. mysql sql does not have dbo (unless you created it)
  6. Every statement must be terminated
  7. There is no unasigned keyword you can default if you wish

This code syntaxes

drop trigger if exists delete_table_checker ;

DELIMITER //
CREATE TRIGGER delete_table_checker 
AFTER DELETE ON transaksi
FOR EACH ROW
begin
DECLARE id_trans VARCHAR(5) default null;
DECLARE trans_number VARCHAR(5) default null; 
DECLARE target_delete VARCHAR(5) default null;
SEt id_trans = old.ID_transaksi;
SET trans_number = SUBSTRING(id_trans,3,3);
SET target_delete = CONCAT('TC',trans_number);
DELETE FROM table_checker WHERE ID_table_checker=target_delete;
end //
DELIMITER ;

But you could do this simply using one delete statement without all those declares and sets..

P.Salmon
  • 17,104
  • 2
  • 12
  • 19