CODEWITHSUNDEEP
sql-servert-sqltriggers
user3104760
user3104760

Reputation: 141

how do we specify the updation in instead of update triggers

I am working on triggers and i wonder something now. What happens when we declare an INSTEAD OF UPDATE trigger? How do we pass the parameter of the inserted tuple into the trigger code? Here is the question from the book i study and my answer for that question is below. I am not sure if that is true but at least i need an idea of inserted or updated tuples for triggers when working around with them. Thanks a lot.

Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The database schema is from the “PC” example of Exercise 2.4.1: Product(maker, model, type) PC(model,speed,ram, hd, price) Laptop(m odel, speed, ram, hd, screen,price) Printer(model, color, type, price)

! c) When making any modification to the Laptop relation, check that the average price of laptops for each manufacturer is at least $1500.

My code is ;

CREATE TRIGGER check_avg on Laptop
select avg(price) as price, manufacturer from Laptop L
group by(manufacturer)
declare @avg = select price from L
if(@avg<1500)
begin
print 'average price of the manufacturer' + select manufacturer from L where price = @avg +'is less than 1500 update is rejected'
end

Upvotes: 1

Views: 793

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

You don't pass parameters to triggers. Triggers inherently have access to two virtual tables called inserted and deleted, which contain the rows that were either inserted or deleted by the operation that fired the trigger.

Upvotes: 1

Related Questions