[database] SQL Triggers

 

create trigger salary_nodecrease
on instructor
after update
as
begin
    set nocount on;

    if exists (
        select 1
        from inserted i
        inner join deleted d on i.instructor_id = d.instructor_id
        where i.salary < d.salary
    )
    begin
        declare @instructor_id varchar(5);

        select top 1 @instructor_id = i.instructor_id
        from inserted i
        inner join deleted d on i.instructor_id = d.instructor_id
        where i.salary < d.salary;
        raiserror('Salary decrease not allowed for instructors %s', 16, 1, @instructor_id);
        rollback transaction;
        return;
    end
end


---이 트리거는 **instructor 테이블의 salary(급여) 필드를 감시하여 
---급여가 줄어드는(update로 인한 감소) 것을 차단하는 역할을 합니다.

 

CREATE TRIGGER check_classroom_capacity
ON classroom
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- 용량 초과 시 오류 발생 및 롤백
  IF EXISTS (SELECT 1 FROM inserted WHERE capacity > 200)
  BEGIN
    RAISERROR('classroom capacity cannot be more than 200', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
  END;

  -- 용량 조건을 통과한 경우에만 삽입
  INSERT INTO classroom(building, room_number, capacity)
  SELECT building, room_number, capacity
  FROM inserted
  WHERE capacity <= 200;
END;