Table1
<code>CREATE TABLE `active_year` (
`ActiveYearId` int(8) UNSIGNED NOT NULL PRIMARY KEY ,
`StartYear` date NOT NULL,
`EndYear` date NOT NULL,
`ActiveId` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB;
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20222023','2022-01-01','2023-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20232024','2023-01-01','2024-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20242025','2024-01-01','2025-12-31',1);
----------------
Table 2
CREATE TABLE `sfs` (
`Id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ActiveYearId` INT(8) UNSIGNED NOT NULL ,
`Name` VARCHAR(20) NOT NULL ,
`Description` VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '20242025', 'sam', 'Hero'), (NULL, '20242025', 'Draad', 'in process');
---------------
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'sam', 'Hero');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Draad', 'in process');
</code>
<code>CREATE TABLE `active_year` (
`ActiveYearId` int(8) UNSIGNED NOT NULL PRIMARY KEY ,
`StartYear` date NOT NULL,
`EndYear` date NOT NULL,
`ActiveId` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB;
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20222023','2022-01-01','2023-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20232024','2023-01-01','2024-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20242025','2024-01-01','2025-12-31',1);
----------------
Table 2
CREATE TABLE `sfs` (
`Id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ActiveYearId` INT(8) UNSIGNED NOT NULL ,
`Name` VARCHAR(20) NOT NULL ,
`Description` VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '20242025', 'sam', 'Hero'), (NULL, '20242025', 'Draad', 'in process');
---------------
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'sam', 'Hero');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Draad', 'in process');
</code>
CREATE TABLE `active_year` (
`ActiveYearId` int(8) UNSIGNED NOT NULL PRIMARY KEY ,
`StartYear` date NOT NULL,
`EndYear` date NOT NULL,
`ActiveId` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB;
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20222023','2022-01-01','2023-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20232024','2023-01-01','2024-12-31',0);
INSERT INTO `active_year`(`ActiveYearId`, `StartYear`, `EndYear`, `ActiveId`) VALUES
('20242025','2024-01-01','2025-12-31',1);
----------------
Table 2
CREATE TABLE `sfs` (
`Id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ActiveYearId` INT(8) UNSIGNED NOT NULL ,
`Name` VARCHAR(20) NOT NULL ,
`Description` VARCHAR(50) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '20242025', 'sam', 'Hero'), (NULL, '20242025', 'Draad', 'in process');
---------------
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'sam', 'Hero');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Draad', 'in process');
When I insert data to table 2 like below :
<code>INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'mical', 'Herro');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Tom', 'in process')
</code>
<code>INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'mical', 'Herro');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Tom', 'in process')
</code>
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) VALUES (NULL, '', 'mical', 'Herro');
INSERT INTO `sfs` (`Id`, `ActiveYearId`, `Name`, `Description`) (NULL, '', 'Tom', 'in process')
The value for column ActiveYearId
will be added by the result of the (
<code>SELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1;
)
my trigger was :
create trigger `update_sfs` after insert on `sfs`
for each row
begin
set new.ActiveYearId = (sELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1);
end;
</code>
<code>SELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1;
)
my trigger was :
create trigger `update_sfs` after insert on `sfs`
for each row
begin
set new.ActiveYearId = (sELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1);
end;
</code>
SELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1;
)
my trigger was :
create trigger `update_sfs` after insert on `sfs`
for each row
begin
set new.ActiveYearId = (sELECT `ActiveYearId` FROM `active_year` WHERE `ActiveId` = 1);
end;
error : updating of new row is not allowed in after trigger
i am using Server version: 10.4.32-MariaDB
New contributor
Abd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.