problème de l'execution d'un trigger

**rezguiinfo** · 10/09/2012, 11h59

Bonjour,

j'ai ce fonction trigger que j'ai crée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER counts
        AFTER INSERT ON logs
        FOR EACH ROW
        BEGIN
        CASE WHEN NEW.lo>DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN
        INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE seen=seen + 1, lastseen=GREATEST(NEW.lo,lastseen), hidden=false;
        UPDATE mne SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.mne;
        UPDATE snare_eid SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE eid=NEW.eid;
        UPDATE programs SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.program;
        INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',1,NEW.lo) ON DUPLICATE KEY UPDATE value=value + 1,updatetime=NEW.lo;
       ELSE
        INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE lastseen=GREATEST(NEW.lo,lastseen), hidden= false;
        UPDATE snare_eid SET lastseen=GREATEST(NEW.lo,lastseen), hidden= false WHERE eid=NEW.eid;
 
        END;

le terminal de mysql m'affiche ces erreurs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
mysql>        CREATE TRIGGER counts
    ->         AFTER INSERT ON logs
    ->         FOR EACH ROW
    ->         BEGIN
    ->         CASE WHEN NEW.lo>DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN
    ->         INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE seen=seen + 1, lastseen=GREATEST(NEW.lo,lastseen), hidden=false;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
mysql>         UPDATE mne SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.mne;
ERROR 1054 (42S22): Unknown column 'NEW.mne' in 'where clause'
mysql>         UPDATE snare_eid SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE eid=NEW.eid;
ERROR 1054 (42S22): Unknown column 'NEW.eid' in 'where clause'
mysql>         UPDATE programs SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.program;
ERROR 1054 (42S22): Unknown column 'NEW.program' in 'where clause'
mysql>         INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',1,NEW.lo) ON DUPLICATE KEY UPDATE value=value + 1,updatetime=NEW.lo;
ERROR 1054 (42S22): Unknown column 'NEW.lo' in 'field list'

aidez moi de les corriger
merci d'avance

**Fred_34** · 10/09/2012, 12h19

Commence par remplacer le case par un if :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER counts
AFTER INSERT ON logs
FOR EACH ROW
BEGIN
	IF ( NEW.lo>DATE_SUB(NOW(), INTERVAL 1 HOUR) )THEN
		INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE seen=seen + 1, lastseen=GREATEST(NEW.lo,lastseen), hidden=false;
		UPDATE mne SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.mne;
		UPDATE snare_eid SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE eid=NEW.eid;
		UPDATE programs SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.program;
		INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',1,NEW.lo) ON DUPLICATE KEY UPDATE value=value + 1,updatetime=NEW.lo;
	ELSE
		INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE lastseen=GREATEST(NEW.lo,lastseen), hidden= false;
		UPDATE snare_eid SET lastseen=GREATEST(NEW.lo,lastseen), hidden= false WHERE eid=NEW.eid;
	END IF;
END;

Il faudrait la structure des tables pour les autres erreurs.

**rezguiinfo** · 10/09/2012, 12h24

Envoyé par Fred_34

Commence par remplacer le case par un if :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER counts
AFTER INSERT ON logs
FOR EACH ROW
BEGIN
	IF ( NEW.lo>DATE_SUB(NOW(), INTERVAL 1 HOUR) )THEN
		INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE seen=seen + 1, lastseen=GREATEST(NEW.lo,lastseen), hidden=false;
		UPDATE mne SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.mne;
		UPDATE snare_eid SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE eid=NEW.eid;
		UPDATE programs SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.program;
		INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',1,NEW.lo) ON DUPLICATE KEY UPDATE value=value + 1,updatetime=NEW.lo;
	ELSE
		INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE lastseen=GREATEST(NEW.lo,lastseen), hidden= false;
		UPDATE snare_eid SET lastseen=GREATEST(NEW.lo,lastseen), hidden= false WHERE eid=NEW.eid;
	END IF;
END;

Il faudrait la structure des tables pour les autres erreurs.

merci pour votre réponse

voici les erreurs après la remplacement de case

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE seen=seen + 1, lastseen=GREATEST(NEW.lo,lastseen), hidden=false;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7
mysql>         UPDATE mne SET seen=seen + 1, lastseen=NEW.lo, hidden= false WHERE crc=NEW.mne;
       ELSE
ERROR 1054 (42S22): Unknown column 'NEW.mne' in 'where clause'
        INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE lastseen=GREATEST(NEW.lo,lastseen), hidden= false;
mysql>         UPDATE snare_eid SET seen=seen + 1, lastseen=NEW.lo, hidden=false WHERE eid=NEW.eid;
 
  END IF;
 
ERROR 1054 (42S22): Unknown column 'NEW.eid' in 'where clause'
        END;mysql>         UPDATE programs SET seen=seen + 1, lastseen=NEW.lo, hidden=false WHERE crc=NEW.program;
ERROR 1054 (42S22): Unknown column 'NEW.program' in 'where clause'
mysql>         INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',1,NEW.lo) ON DUPLICATE KEY UPDATE value=value + 1,updatetime=NEW.lo;
ERROR 1054 (42S22): Unknown column 'NEW.lo' in 'field list'
mysql>        ELSE
    ->         INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON DUPLICATE KEY UPDATE lastseen=GREATEST(NEW.lo,lastseen), hidden= false;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE
        INSERT INTO hosts(host,lastseen,seen) VALUES (NEW.host,NEW.lo,1) ON' at line 1
mysql>
mysql>   END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1
mysql>
mysql>         END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

**ericd69** · 10/09/2012, 16h01

salut,

toutes les colonnes que tu décris avec new n'existent pas, c'est ce qu'il te dit...

vérifie la structure de ta table counts

**rezguiinfo** · 10/09/2012, 16h06

Envoyé par ericd69

salut,

toutes les colonnes que tu décris avec new n'existent pas, c'est ce qu'il te dit...

vérifie la structure de ta table counts

merci pour votre réponse
main les colonnes sont existe
tabler logs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+----------+---------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+
| Field    | Type                                                                                                                | Null | Key | Default             | Extra          |
+----------+---------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+
| id       | bigint(20) unsigned                                                                                                 | NO   | PRI | NULL                | auto_increment |
| host     | varchar(128)                                                                                                        | NO   | MUL | NULL                |                |
| facility | enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23') | NO   | MUL | NULL                |                |
| severity | enum('0','1','2','3','4','5','6','7')                                                                               | NO   | MUL | NULL                |                |
| program  | int(10) unsigned                                                                                                    | NO   | MUL | NULL                |                |
| msg      | varchar(2048)                                                                                                       | NO   |     | NULL                |                |
| mne      | int(10) unsigned                                                                                                    | NO   | MUL | NULL                |                |
| eid      | int(10) unsigned                                                                                                    | NO   | MUL | 0                   |                |
| suppress | datetime                                                                                                            | NO   | MUL | 2010-03-01 00:00:00 |                |
| counter  | int(11)                                                                                                             | NO   |     | 1                   |                |
| fo       | datetime                                                                                                            | NO   | MUL | NULL                |                |
| lo       | datetime                                                                                                            | NO   | PRI | NULL                |                |
| notes    | varchar(255)                                                                                                        | NO   |     | NULL                |                |
+----------+---------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+----------------+

table hosts

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| host     | varchar(128)     | NO   | UNI | NULL    |                |
| lastseen | datetime         | NO   |     | NULL    |                |
| seen     | int(10) unsigned | NO   |     | 1       |                |
+----------+------------------+------+-----+---------+----------------+

merci d'avance

**ericd69** · 10/09/2012, 16h17

mais moi je te parle de ta table counts... c'est sur elle que porte le trigger...

problème de l'execution d'un trigger

SQL Procédural MySQL

Vue hybride

Discussions similaires

Partager

Partager