Triggers!

[kaffeburk]

Im trying to create a siple trigger using/chaning the demo example.

The trigger in design mode:

BEGIN
UPDATE test.articles SET = test.articles.nofs_instock
(SELECT SUM(stock.the_change) FROM test.stock
WHERE stock.id_article = articles.id_art )


END

In text-mode:

CREATE TRIGGER test.trigger1 AFTER INSERT
ON test.articles FOR EACH ROW BEGIN
UPDATE test.articles SET = test.articles.nofs_instock
(SELECT SUM(stock.the_change) FROM test.stock
WHERE stock.id_article = articles.id_art )


END

When i try to save it i get an error (that i cant copy...)

You have an error i n yoyr SQL syntax ...Update test.article SER = test.articles.nofs_instock (select SUM (stock.the_' at line 3


*** The database:


CREATE DATABASE /*!32312 IF NOT EXISTS*/ test
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

USE test;

--
-- Table structure for table articles
--
CREATE TABLE `articles` (
`id_art` int(11) NOT NULL auto_increment,
`nofs_instock` int(11) default NULL,
PRIMARY KEY (`id_art`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table stock
--
CREATE TABLE `stock` (
`id_change` int(11) NOT NULL auto_increment,
`id_article` int(11) default NULL,
`the_change` int(11) default NULL,
PRIMARY KEY (`id_change`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[Duke]

You have written incorrect trigger body. Here is the correct one.

Code: Select all

CREATE TRIGGER test.trigger1 AFTER INSERT
ON test.articles FOR EACH ROW BEGIN
UPDATE test.articles SET  nofs_instock =
(SELECT SUM(stock.the_change) FROM test.stock 
WHERE articles.id_art = stock.id_article = articles.id_art);
END

[kaffeburk]

Thanx, now it works. The ";" was missing.

In the "how to / helpfile" its the same:

UPDATE demobase.dept SET demobase.dept.salsum =
(SELECT SUM(demobase.emp.sal) FROM demobase.emp
WHERE demobase.emp.deptno = demobase.dept.deptno)

--

In your answer you have three entityis in the where clasule:
WHERE articles.id_art = stock.id_article = articles.id_art);

is this neccisary?

/Pär

[Duke]

No, it's not. It was my mistake.