Trouble pulling the Trigger

[Brett Simpson]

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)