can a trigger know info about the process raises it?

[GuardianOfTheFlame]

Hi all,
in my database, I have:
- a table "Visit"
- a stored procedure "sp_create_visit"
- a table "logs"

CREATE TABLE `logs` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`operation_type` int(2) NOT NULL DEFAULT '1' COMMENT '1=no changes; 2=insert, 3=update, 4=delete',
	`users_id` int(11) unsigned NOT NULL DEFAULT '1000',
	`table_name` varchar(100) NOT NULL DEFAULT '',
	`row_id` int(11) unsigned NOT NULL DEFAULT '0',
	`log_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	KEY `operation_type` (`operation_type`),
	KEY `users_id` (`users_id`),
	KEY `table_name` (`table_name`),
	KEY `row_id` (`row_id`),
	KEY `log_date` (`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=9633 DEFAULT CHARSET=latin1 COMMENT='Log table'

- an after insert trigger on visit to log the changes

CREATE TRIGGER `t_Visit_after_insert` AFTER  ON `Visit`
FOR EACH ROW BEGIN
INSERT INTO `logs`(`operation_type`, `users_id`, `table_name`, `row_id`, `log_date`) VALUES (2, @logged_user_id, "Visit", NEW.id, NOW());
END

inside the trigger, is there a way to know if the insert command is executed inside a stored procedure?

I would like to track this information in my log system; something like:
- if I insert a row using a direct query:
id | operation_type | users_id | table_name | row_id | log_date | caller
1 | 2 | 1000 | Visit | 20 | 2009-12-04 15:02:14 | insert command
- if I call "sp_create_visit" procedure:
id | operation_type | users_id | table_name | row_id | log_date | caller
1 | 2 | 1000 | Visit | 20 | 2009-12-04 15:02:14 | sp_create_visit

thanks,
Matteo

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[ingresman]

You could put a column in that gets set depending on where thye insert got called.

CREATE TRIGGER `t_Visit_after_insert` AFTER  ON `Visit`
FOR EACH ROW BEGIN
INSERT INTO `logs`(`operation_type`, `users_id`, `table_name`, `row_id`, `log_date`), source, VALUES (2, @logged_user_id, "Visit", NEW.id, NOW(), "trigger");
END

 

[GuardianOfTheFlame]

I don't want to know the source of the INSERT statement that logs the values: this should be not a problem cause I know that I'm in the trigger.
What I want to know is the source of the INSERT statement that raise the trigger

this is the scenario:
- my application send the following command to MySQL:
CALL sp_create_visit(...);
- sp_create_visit insert a row in the "Visit" table
- the AFTER INSERT trigger is raised: can the trigger know that it was raised by an insert command inside the stored procedure? Are there some process variables that I can read inside the trigger code?

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[ingresman]

I don't know about any thing you could read but you update the location column in the stored procedure to say to "sp" and go from there.