Reputation: 146
I have seen similar questions to mine, however nothing seems to fit the scenario exactly.
I have 2 tables, bills and accounts.
If a bill is pushed into the bills table and there is not already an account, I want to create a record in the accounts table.
I have:
...
BEFORE INSERT ON bills
FOR EACH ROW
BEGIN
INSERT INTO accounts (id, field1, field2, field3, field4)
SELECT accountID, 'value1', value2, value3, value4
FROM bills
WHERE ((SELECT LEFT(accountID, 5) = 'XXXXX' |
(SELECT LEFT(accountID, 5) = 'XXXXX' |
(SELECT LEFT(accountID, 5) = 'XXXXX' |
(SELECT LEFT(accountID, 5) = 'XXXXX' |
(SELECT LEFT(accountID, 5) = 'XXXXX')) &&
accountID NOT IN (SELECT id from accounts); ## I know this line is not right
END$$
From What I have read elsewhere on SO my options seem to be ON DUPLICATE KEY UPDATE or INSERT IGNORE, neither of which I can seem to make work, nor can I find an example with INSERT, SELECT, FROM and WHERE all involved.
What am I missing?
EDIT: I can make it insert the record, but depending on how I have tried it I always get either "duplicate id" error, or it doesnt insert the record.
Upvotes: 0
Views: 584
Reputation: 1335
Havent coded in MySQL for quite a while hence please excuse if there is some small syntax error. However, you will get the idea what needs to be done in the following code:
before insert on bills
for each row
begin
declare @li_cnt int;
select count('') into @li_cnt from accounts where accounts.id_account = inserted.id_account;
if @li_cnt = 0 then
insert into accounts (id_account) values (inserted.id_account)
end
end
However, this type of issue should be handled at the middle tier application server level and not at the database trigger level where one should be actually implementing strict policies and not complying to business requirements. So in that respect, assuming your app to be a PHP web app, you should have the bill's class method save() which should be checking the same and inserting the value in accounts table using the accounts table's class and then inserting the row in the bills table. Using this method one can really make modern sophisticated logic e.g. you can restrict insertion of bills for a specific account if the accounts class method that you call from the bill insertion sends back a decline response. Although it seems you require a simple insertion of data into accounts table when a bill is inserted with an accounts code / ID that doesnt exist in the master accounts table, I still feel coding the right way even in simple requirements makes for a good platform for an app to later scale and become pretty complicated with minimum issues maintenance cost.
Upvotes: 1