I'm having some trouble writing this trigger and wasn't sure what I'm doing wrong. My goal is after a row has been added to tcphdr it will pull data from several other tables based on the NEW rows event.cid and event.timestamp and insert it into acid_event. However I'm not sure how to tell the WHERE portion what the NEW cid and timestamp is.
DELIMITER | CREATE TRIGGER tcphdr_trig AFTER INSERT ON tcphdr FOR EACH ROW BEGIN INSERT INTO acid_event (sid, cid, signature, sig_name, sig_class_id, sig_priority, timestamp, ip_src, ip_dst, ip_proto, layer4_sport, layer4_dport) SELECT event.sid as sid, event.cid as cid, signature, sig_priority, sig_class_id, timestamp, ip_src, ip_dst, ip_proto, tcp_sport as layer4_sport, tcp_dport as layer4_dport FROM event INNER JOIN signature ON (signature = signature.sig_id) INNER JOIN iphdr ON (event.sid=iphdr.sid AND event.cid=iphdr.cid) LEFT JOIN tcphdr ON (event.sid=tcphdr.sid AND event.cid=tcphdr.cid) WHERE event.cid = NEW.cid AND event.timestamp = NEW.timestamp; END; |ERROR 1054 (42S22): Unknown column 'timestamp' in 'NEW' mysql> delimiter ; mysql> show columns from event; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | signature | int(10) unsigned | NO | MUL | | | | timestamp | datetime | NO | PRI | | | +-----------+------------------+------+-----+---------+-------+ 4 rows in set (0.03 sec) mysql> show columns from acid_event; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | signature | int(10) unsigned | NO | MUL | | | | sig_name | varchar(255) | YES | MUL | | | | sig_class_id | int(10) unsigned | YES | MUL | | | | sig_priority | int(10) unsigned | YES | MUL | | | | timestamp | datetime | NO | PRI | | | | ip_src | int(10) unsigned | YES | MUL | | | | ip_dst | int(10) unsigned | YES | MUL | | | | ip_proto | int(11) | YES | MUL | | | | layer4_sport | int(10) unsigned | YES | MUL | | | | layer4_dport | int(10) unsigned | YES | MUL | | | +--------------+------------------+------+-----+---------+-------+ 12 rows in set (0.03 sec) mysql> show columns from signature; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | sig_id | int(10) unsigned | NO | PRI | | auto_increment | | sig_name | varchar(255) | NO | MUL | | | | sig_class_id | int(10) unsigned | NO | MUL | | | | sig_priority | int(10) unsigned | YES | | | | | sig_rev | int(10) unsigned | YES | | | | | sig_sid | int(10) unsigned | YES | | | | +--------------+------------------+------+-----+---------+----------------+ 6 rows in set (0.02 sec) mysql> show columns from iphdr; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | ip_src | int(10) unsigned | NO | MUL | | | | ip_dst | int(10) unsigned | NO | MUL | | | | ip_ver | tinyint(3) unsigned | YES | | | | | ip_hlen | tinyint(3) unsigned | YES | | | | | ip_tos | tinyint(3) unsigned | YES | | | | | ip_len | smallint(5) unsigned | YES | | | | | ip_id | smallint(5) unsigned | YES | | | | | ip_flags | tinyint(3) unsigned | YES | | | | | ip_off | smallint(5) unsigned | YES | | | | | ip_ttl | tinyint(3) unsigned | YES | | | | | ip_proto | tinyint(3) unsigned | NO | | | | | ip_csum | smallint(5) unsigned | YES | | | | +----------+----------------------+------+-----+---------+-------+ 14 rows in set (0.03 sec) mysql> show columns from tcphdr; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | sid | int(10) unsigned | NO | PRI | | | | cid | int(10) unsigned | NO | PRI | | | | tcp_sport | smallint(5) unsigned | NO | MUL | | | | tcp_dport | smallint(5) unsigned | NO | MUL | | | | tcp_seq | int(10) unsigned | YES | | | | | tcp_ack | int(10) unsigned | YES | | | | | tcp_off | tinyint(3) unsigned | YES | | | | | tcp_res | tinyint(3) unsigned | YES | | | | | tcp_flags | tinyint(3) unsigned | NO | MUL | | | | tcp_win | smallint(5) unsigned | YES | | | | | tcp_csum | smallint(5) unsigned | YES | | | | | tcp_urp | smallint(5) unsigned | YES | | | | +-----------+----------------------+------+-----+---------+-------+ 12 rows in set (1.39 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
