Tips on how to create a specific trigger

Question

Hello there! I am new into SQL and am trying to solve a few problems by writing triggers for a proposed problem in order to fix/guarantee certain issues.

The problem goes like this:
Create a trigger that guarantees that a budget can only be associated with accounts that belong to the user of this same budget.

This is a picture of the Physical model used:

I can provide further information needed, just let me know if you don't understand or aren't clear at any part. Also, thanks in advance for any help on this topic, I am struggling on thinking of a way to do this trigger so any help is appreciated!

Answer 1

Here are two triggers for you. You may need a third one on Accounts, but I leave that as an exercise.

CREATE TRIGGER Budget_Accounts_tri ON Budget_Accounts AFTER INSERT, UPDATE AS
  IF EXISTS (SELECT *
             FROM   inserted i
             JOIN   Budgets B ON i.IDBudget = B.IDBudget
             JOIN   Accounts A ON i.IDAccount = A.IDAccount
             WHERE  A.IDUser = B.IDUser)
  BEGIN 
     ROLLBACK TRANSACTION
     RAISERROR('Budget is using accounts for a different user.', 16, 1)
  END
go
CREATE TRIGGER Budget_Tri ON Budget AFTER INSERT, UPDATE AS
   IF EXISTS (SELECT *
              FROM   inserted i
              JOIN   Budget_Accounts BA ON i.IDBudget = BA.IDBudget
              JOIN   Accounts A ON BA.IDAaccount = A.IDAccount)
  BEGIN 
     ROLLBACK TRANSACTION
     RAISERROR('Budget is using accounts for a different user.', 16, 1)
  END

The table "inserted " that flourishes is a virtual table which holds the row inserted by an INSERT statement, and the result rows from an UPDATE statement. In triggers you can also used "deleted" for the deleted rows in a DELETE statement, and a before-image of a row for an UPDATE statement.

I'm a little curious about the data model. It suggests that every user have their own set of accounts. That seems a little odd to me, but I don't know what this is a data model for.

Answer 2

hi there , i need the solution for this project @Harry