I'm implementing a database for a social network, this social network must have adds, for this purpose I've implemented a table named brands, this table has a viewsCounter column that everytime you click on a brand's homepage the viewsCounter increments by 1 the already existing views.
This is the brands table:
CREATE TABLE brands (
brandId int NOT NULL,
brandName varchar(45) NOT NULL,
viewsCounter int DEFAULT NULL,
PRIMARY KEY (brandId));
The brands table looks something like this:
| brandId | brandName | viewsCounter |
|---|---|---|
| 0 | adidas | 0 |
| 1 | apple | 2 |
| 2 | cocacola | 6 |
Now, there's also a profits table, this table has a moneyReceived column and a profitDate column, every 5 views on the viewsCounter the moneyReceived column associated to that specific brand must increase by 1 and the profitDate must update to the specific date when that profit was made.
Here's the profits table:
CREATE TABLE profits (
profitId int NOT NULL,
moneyReceived int NOT NULL,
profitDate date DEFAULT NULL,
brandId_profits int DEFAULT NULL,
PRIMARY KEY (profitId),
KEY fk_profits_brands_idx (brandId_profits),
CONSTRAINT fk_profits_brands
FOREIGN KEY (brandId_profits)
REFERENCES brands (brandId)
ON DELETE RESTRICT ON UPDATE RESTRICT);
The profits table looks like this:
| profitId | moneyReceived | profitDate | brandId_profits |
|---|---|---|---|
| 0 | 0 | 0 | |
| 1 | 0 | 1 | |
| 2 | 0 | 2 |
Ok, so now I'm making a trigger that every 5 views on the viewsCounter from the brands table updates the profits table by increasing the moneyReceived column associated to that specific brand by 1 and also update the date from the profitDate.
Here's the trigger code i've made:
CREATE TRIGGER trigger_brands_profits
AFTER UPDATE on brands
FOR EACH ROW UPDATE profits
SET moneyReceived = moneyReceived 1, profitDate=now()
WHERE (brands.brandId = brandId_profits) and (brands.viewsCounter % 5 = 0);
But after I implement the trigger and try to add any number of views to the viewsCounter I get ERROR 1054: Unknown column 'brands.brandId' in 'where clause'
I've already tried many combinations trying to declare variables but still doesn't works.
CodePudding user response：
in your trigger, you are trying to update the profits table, but you reference columns in the brands table (brands.brandId and brands.viewsCounte) without joining to that table.
i'm not entirely certain which version of sql you are using, but your trigger's UPDATE statement needs to be something with a JOIN to the brands table; something like this:
UPDATE profits
SET moneyReceived = moneyReceived 1, profitDate=GETDATE()
FROM profits
JOIN brands ON profits.brandId_profits = brands.brandid
WHERE (brands.brandId = brandId_profits) and (brands.viewsCounter % 5 = 0);
UPDATE: sorry - looks like you're using mysql maybe?
here's the UPDATE with JOIN statement for mysql ...
UPDATE profits
JOIN brands ON brandId = brandId_profits
SET moneyReceived = moneyReceived 1, profitDate=now()
WHERE (brands.brandId = brandId_profits) AND (brands.viewsCounter % 5 = 0);
CodePudding user response：
Try to get the value when it is to be inserted and replace it in the update as in the example picture belowenter image description here
BTW, here is a link if it may help: