Consider a brokerage firm database with relations Holdings(AccountId, StockSymbo

Question

Consider a brokerage firm database with relations Holdings(AccountId, StockSymbol, CurrentPrice, Quantity) and Balance(AccountId, Balance). Write the triggers for maintaining the correctness of the account balance when stock is bought (a tuple is added to Holdings or Quantity is incremented), sold (a tuple is deleted from Holdings or Quantity is decremented), or a price change occurs. Solve the problem using both row-level and statement-level triggers. Give an example of a situation when row-level triggers are more appropriate for the above problem and when statement-level triggers are more appropriate.

Explanation / Answer

Creation of table Balance:

Create Table Balance(AccountId int primary key not null, Balance int)

Creation of table Holdings:

Create Table Holdings(AccountId int, StockSymbol varchar(50),CurrentPrice int , Quantity int)

Adding a Tuple:

CREATE TRIGGER trgAfterInsert ON [dbo].Balance

FOR INSERT

AS

declare @accId int

declare @stockSymbol varchar(50)

declare @currentPrice int

declare @quantity int

select @accId=i.AccountId from Balance i;

print @accId

Set @currentPrice=100

Set @stockSymbol='Pepsi'

Set @quantity=1

insert into Holdings values(@accId,@stockSymbol,@currentPrice,@quantity);

PRINT 'AFTER INSERT trigger fired.'

GO

Delete a Tuple:

Create TRIGGER trgAfterDelete ON [dbo].Balance

FOR DELETE

AS

declare @accId int

declare @stockSymbol varchar(50)

declare @currentPrice int

declare @quantity int

select @accId=d.AccountId from deleted d;

Delete from Holdings where AccountId=@accId

PRINT 'Delete trigger fired.'

GO

Output:

1. Insert into Balance values(123,2000)

Output:

123

(1 row(s) affected)
AFTER INSERT trigger fired.

(1 row(s) affected)

2. Delete from Balance where AccountId=123

Output:

123

(1 row(s) affected)
Delete trigger fired.

(1 row(s) affected)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.