CODEWITHSUNDEEP
mysqldatabasetriggers
deez
deez

Reputation: 146

MySql Trigger that inserts a record in another table if id isn't a duplicate

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

Answers (1)

somnath
somnath

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

Related Questions