트리거는 테이블의 특정 이벤트에서 실행되는 특별한 종류의 저장 프로시저입니다. 트리거는 특정 테이블과 연결되며 가장 자주 데이터에 대한 보호 역할을 수행합니다. 섹션 1.5에서 데이터 무결성에 대해 이야기하고 트리거가 가장 강력한 보호 기능이라고 언급했습니다. 당시 우리는 정보가 거의 없었기 때문에 제한 사항만 자세히 고려했으며 트리거와 관련하여 일반적인 단어로 제한했습니다.
트리거가 응답할 수 있는 세 가지 이벤트(데이터 추가, 변경 및 삽입)가 있습니다. 데이터에 영향을 미치려는 모든 시도. 테이블에서 데이터를 삽입, 업데이트 또는 삭제하려고 시도하고 해당 테이블에서 해당 작업에 대한 트리거가 선언되면 자동으로 호출됩니다. 우회할 수 없습니다. 내장 프로시저와 달리 트리거는 직접 호출할 수 없으며 매개변수를 받거나 받지 않습니다.
트리거는 제약 조건보다 느리다는 단점이 있지만 낮은 수준의 데이터 무결성을 보장하는 가장 좋은 방법입니다. 트리거의 주요 장점은 복잡한 실행 로직을 포함할 수 있다는 것입니다. 그들은 할 수있다:
- 데이터베이스의 종속 테이블을 계단식으로 변경하여 CHECK 제약 조건보다 더 포괄적인 데이터 무결성을 제공합니다.
- 개별 오류 메시지를 선언합니다.
- 정규화되지 않은 데이터를 포함합니다.
- 변경 전과 후의 데이터 상태를 비교합니다.
이것이 주요 이점이며 이 섹션이 끝나면 더 많은 이점이 있음을 알 수 있습니다.
트리거를 사용하여 종속 데이터베이스 테이블에서 변경 또는 삭제를 계단식으로 만들 수 있습니다. 예를 들어, tpPhoneType 테이블에서 데이터를 삭제하는 트리거는 삭제되는 전화 유형 식별자와 연결된 행이 있는 다른 테이블의 해당 행을 삭제할 수 있습니다. 이것이 완료되지 않으면 연결이 끊어지고 데이터베이스 무결성이 손상된 것으로 간주됩니다.
CHECK 제약 조건과 달리 트리거는 다른 테이블의 필드를 참조할 수 있습니다. 예를 들어, tbPosition 테이블에 추가 트리거를 배치하여 추가되는 기본 위치를 조회하고 해당 위치에 직원이 있는지 확인할 수 있습니다.
3.4.1. 트리거 생성
CREATE TRIGGER 문을 사용하여 트리거를 생성합니다. 명령문은 트리거가 선언된 테이블, 트리거가 실행되는 이벤트 및 트리거에 대한 개별 명령문을 지정합니다. 일반적으로 명령은 목록 3.2에 나와 있습니다.
목록 3.2. CREATE TRIGGER 명령의 일반 보기
CREATE TRIGGER trigger_name ON ( 테이블 | 보기 ) [ WITH ENCRYPTION ] ( ( ( FOR | AFTER | INSTEAD OF ) ( [ INSERT ] [ , ] [ UPDATE ] ) [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ ( IF UPDATE ( 열 ) [ ( AND | OR ) 업데이트(열) ] [ ...n ] | IF (COLUMNS_UPDATED() (bitwise_operator) updated_bitmask) ( comparison_operator ) column_bitmask [ ...n ] ) ] sql_statement [ ...n ] ) )
우리가 고려하기 전에 실제 예, 두 가지 논평을 살펴보자. 트리거를 생성하면 트리거에 대한 정보가 sysobjects 및 syscomments 시스템 테이블에 삽입됩니다. 기존 트리거와 동일한 이름으로 트리거를 생성하면 새 트리거가 기존 트리거를 덮어씁니다. SQL Server는 시스템 테이블에 사용자 선언 트리거 추가를 지원하지 않으므로 시스템 테이블에 대해 트리거를 생성할 수 없습니다.
SQL Server에서는 트리거 본문에 다음 문을 사용할 수 없습니다.
- 데이터베이스 변경;
- 데이터베이스 생성;
- 디스크 초기화;
- 디스크 크기 조정;
- 데이터베이스 삭제;
- 로드 데이터베이스;
- 로드 로그;
- 재구성
- 데이터베이스 복원;
- 복원 로그.
이 모든 문장을 기억하지 않으려면 데이터베이스 구조를 변경할 수 없다는 것을 기억하는 것이 더 쉽습니다.
3.4.2. 트리거에서 변경 사항 롤백
트리거 선언에는 해당하는 BEGIN TRANSACTION이 없더라도 ROLLBACK TRANSACTION 문이 포함될 수 있습니다. 우리가 이미 말했듯이, 모든 변경에 대해 SQL 서버에는 트랜잭션이 필요합니다. 명시적으로 지정하지 않으면 암시적 트랜잭션이 생성됩니다. ROLLBACK TRANSACTION 문이 실행되면 트리거의 모든 변경 사항과 트리거를 발생시킨 변경 사항이 롤백됩니다.
롤백을 사용할 때 다음 사항을 고려해야 합니다.
- ROLLBACK TRANSACTION 문이 실행되면 트랜잭션의 내용이 롤백됩니다. ROLLBACK TRANSACTION 뒤에 문이 있으면 해당 문이 실행됩니다. RETURN 명령을 사용할 때는 필요하지 않을 수 있습니다.
- 트리거가 트랜잭션을 롤백하면 사용자 정의, 그런 다음 완전히 롤백됩니다. 모듈을 실행하기 위해 트리거가 실행되면 모듈에 대한 명령도 취소됩니다. 후속 모듈 문은 실행되지 않습니다.
- 트리거 코드에서 ROLLBACK TRANSACTION 사용을 최소화해야 합니다. 트랜잭션을 롤백하면 완료되지 않은 모든 작업 때문에 추가 작업이 생성됩니다. 이 순간거래에서 불완전합니다. 이는 성능에 부정적인 영향을 미칩니다. 모든 것이 검증된 후에 트랜잭션을 실행하여 트리거에서 롤백할 필요가 없습니다.
계속해서 실제 방아쇠를 연구합시다. 예를 들어 트랜잭션만 롤백하고 마지막으로 실제 예제와 롤백 작동 방식을 확인하는 트리거를 생성해 보겠습니다.
롤백 트랜잭션으로 업데이트하기 위해 dbo.tbPeoples에서 트리거 생성 u_tbPeoples
항상 그렇듯이 모든 작업을 줄로 나누면 보기 쉽고 주제를 더 쉽게 읽고 연구할 수 있습니다. 첫 번째 줄에는 CREATE TRIGGER 문 뒤에 이름이 있습니다. 트리거 이름을 지정할 때 다음 규칙을 따릅니다.
- 이름은 u(업데이트), i(삽입) 또는 d(삭제) 중 하나 이상의 문자로 시작합니다. 이 문자를 통해 어떤 작업이 트리거되는지 쉽게 결정할 수 있습니다.
- 밑줄 다음에 트리거가 생성되는 테이블의 이름이 옵니다.
이름 뒤에는 키워드 ON과 트리거가 생성되는 테이블의 이름이 옵니다.
두 번째 줄에는 FOR 키워드와 트리거가 실행되는 이벤트가 포함됩니다. 에 이 예 UPDATE 작업이 지정되었습니다. 업데이트. 그리고 마침내 예어 AS는 트리거의 본체입니다. 실행할 명령. 이 예에서는 ROLLBACK TRANSACTION, 즉 하나의 명령만 실행됩니다. 롤백.
이제 트리거가 실행되도록 tbPeoples 테이블의 데이터를 변경해 보겠습니다.
업데이트 tbPeoples SET vcFamil="dsfg"
이 예에서는 tbPeoples 테이블의 모든 레코드에 대해 "vcFamil" 필드의 내용을 변경하려고 합니다. 우리는 왜 노력하고 있습니까? 예, 변경 시 트랜잭션 롤백이 있는 트리거가 작동하기 때문입니다. 데이터를 가져와 모든 데이터가 제자리에 있고 변경되지 않았는지 확인합니다.
SELECT * FROM tbPeoples
데이터를 업데이트할 때 트랜잭션을 시작하지 않았음에도 불구하고 ROLLBACK TRANSACTION 문은 오류 없이 실행되어 변경 사항이 취소되었습니다.
3.4.3. 트리거 변경
기존 트리거의 선언을 변경하려는 경우 삭제 및 재생성 없이 변경할 수 있습니다. 존재하지 않는 트리거 선언의 개체를 참조할 수 있습니다. 광고를 생성할 때 일부 개체가 존재하지 않으면 경고만 표시됩니다.
ALTER TRIGGER 문은 트리거를 업데이트하는 데 사용됩니다. 연산자의 일반적인 보기는 목록 3.3에서 볼 수 있습니다.
목록 3.3. 트리거 업데이트 문
ALTER TRIGGER trigger_name ON (테이블 | 뷰) [ WITH ENCRYPTION ] ( ( ( (FOR | AFTER | INSTEAD OF) ( [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] ) [ NOT FOR REPLICATION ] AS sql_statement [ . .n ] ) | ( ( FOR | AFTER | 대신) ( [ INSERT ] [ , ] [ UPDATE ] ) [ NOT FOR REPLICATION ] AS ( IF UPDATE (열) [ ( AND | OR ) UPDATE (열) ] [ . ..n ] |IF(COLUMNS_UPDATED() ( bitwise_operator ) updated_bitmask) ( comparison_operator ) column_bitmask [ ...n ] ) sql_statement [ ...n ] ) )
항목을 추가할 때도 반응하도록 u_tbPeoples 트리거를 변경해 보겠습니다. 이를 위해 다음 쿼리를 실행합니다.
ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples 업데이트, 롤백 트랜잭션으로 삽입
보시다시피 update 문은 트리거를 만드는 것과 비슷합니다. 차이점은 첫 번째 줄에 ALTER TRIGGER 문이 포함되어 있다는 것입니다. 두 번째 줄에 변경 사항이 있었고 이제 트리거는 업데이트(UPDATE)뿐만 아니라 추가(INSERT) 시에도 작동합니다.
항목을 추가하고 그 후에 항목이 추가되지 않았는지 확인하십시오. 이제 변경 롤백 트리거가 항목 추가에서도 작동하기 때문입니다. 항목 추가의 예:
INSERT INTO tbPeoples(vcFamil) VALUES("PETECHKIN")
특정 트리거 또는 테이블당 모든 트리거를 활성화하거나 비활성화할 수 있습니다. 트리거가 비활성화되면 여전히 테이블에 존재하지만 지정된 이벤트에서 실행되지 않습니다. ALTER TABLE 명령으로 트리거를 비활성화할 수 있습니다. 일반적으로 연산자는 다음과 같습니다.
ALTER TABLE 테이블 (ENABLE | DISABLE) TRIGGER (ALL | trigger_name [,..n])
보시다시피 변경 사항은 트리거가 아니라 테이블 자체에 영향을 줍니다. 이전에 생성된 트리거를 비활성화해 보겠습니다.
ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples
첫 번째 줄에는 ALTER TABLE 문과 변경할 테이블의 이름을 씁니다. 두 번째 줄에는 키워드 DISABLE(비활성화) 또는 ENABLE(활성화) 및 키워드 TRIGGER가 포함되어야 합니다. 그리고 마지막으로 방아쇠의 이름입니다.
지금 tbPeoples 테이블에 게시물을 추가해 보세요. 이번에는 모든 것이 잘 될 것입니다.
트리거 이름 대신 ALL 키워드를 지정할 수 있습니다. 이렇게 하면 지정된 테이블의 모든 트리거가 영향을 받습니다. 예를 들어 다음 예에서는 모든 트리거를 활성화합니다.
ALTER TABLE tbPeoples ENABLE TRIGGER ALL
3.4.4. 트리거 제거
DROP TRIGGER 문을 사용하여 트리거를 제거할 수 있습니다. 연결된 테이블이 삭제되면 자동으로 삭제됩니다.
방아쇠 제거 예:
DROP TRIGGER u_tbPeoples
이 작업을 수행하려면 적절한 권한이 있어야 합니다. 프로시저, 함수 및 보기 개체와 마찬가지로 쉼표로 구분된 이름을 지정하여 여러 트리거를 한 번에 제거할 수 있습니다.
3.4.5. 트리거는 어떻게 작동합니까?
이 장에서는 방법에 대해 자세히 살펴보겠습니다. 다른 유형트리거. 이를 위해 가능한 한 현실에 가까운 예제를 많이 작성함과 동시에 Transact-SQL 프로그래밍 및 트리거 생성에 대한 모범 사례를 얻을 것입니다.
삽입 트리거
레코드 추가 트리거가 실행되면 어떻게 됩니까? 서버가 수행하는 단계를 살펴보겠습니다.
- 사용자는 INSERT 문을 실행하여 레코드를 추가합니다.
- 트리거가 호출됩니다.
트리거가 호출될 때 데이터베이스에 물리적 변경이 아직 발생하지 않았습니다. 트리거 본문에서 삽입된 테이블로 추가되는 레코드를 볼 수 있습니다. 아니요, 데이터베이스에 그런 테이블이 없습니다. 테이블에 삽입할 행의 복사본이 포함된 논리적 테이블이 삽입되었습니다. 보다 구체적으로 INSERT 문의 활동에 대한 로그를 포함합니다. 이 테이블의 데이터를 사용하여 삽입할 데이터를 결정할 수 있습니다. 삽입된 테이블의 행은 항상 트리거 테이블에서 하나 이상의 행을 복제합니다.
모든 데이터 수정 활동은 기록되지만 트랜잭션 로그의 정보는 읽을 수 없습니다. 그러나 삽입된 테이블을 사용하면 변경 사항을 참조하고 정의할 수 있습니다.
삽입된 테이블은 항상 트리거가 배치된 테이블과 동일한 구조를 포함합니다.
직원 이름이 Vasya와 같은 레코드를 추가하기 위해 트리거를 사용하지 않도록 합시다. 이러한 트리거의 예는 목록 3.4에서 볼 수 있습니다.
목록 3.4. 삽입된 테이블 사용
CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM 삽입 IF @Name="Vanya" BEGIN PRINT "ERROR" ROLLBACK TRANSACTION END
이 예에서는 레코드를 추가하는 트리거를 생성합니다. 트리거 내에서 길이가 50자인 varchar 유형의 변수 @Name을 선언합니다. 삽입된 테이블의 "vcName" 필드 내용을 이 변수에 저장합니다. 다음으로 이름이 Vasya와 같은지 확인한 다음 오류를 보고하고 트랜잭션을 롤백합니다. 그렇지 않으면 라인이 성공적으로 추가됩니다.
자료를 통합하기 위해 "vcName" 필드에 대해 null 값을 금지하는 트리거를 작성해 보겠습니다. 이러한 트리거에 대한 코드는 목록 3.5에서 볼 수 있습니다.
CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM 삽입 WHERE vcName is NULL) BEGIN PRINT "오류, vcName 필드를 완료해야 합니다." ROLLBACK TRANSACTION END
이 예에서는 삽입된 테이블에 다음과 같은 레코드가 있는지 확인합니다. 제로 값필드 "vcName"을 선택한 다음 추가 시도를 롤백합니다.
DELETE 트리거
삭제 트리거가 실행되면 레코드를 추가할 때와 동일한 논리가 트리거됩니다.<.p>
- 사용자는 DELETE 문을 실행하여 레코드를 추가합니다.
- 서버는 트랜잭션 로그에 요청에 대한 정보를 저장합니다.
- 트리거가 호출됩니다.
- 데이터의 변경 및 물리적 변경 확인.
삭제된 행은 삭제된 행을 보는 데 사용할 수 있는 삭제된 테이블에 배치됩니다. DELETE 문의 로그 데이터를 참조하는 논리 테이블입니다.
다음을 고려해야 합니다.
- 행이 삭제된 테이블에 추가되면 데이터베이스 테이블에는 여전히 존재합니다.
- 메모리는 삭제된 테이블에 할당되므로 항상 캐시에 있습니다.
- 삭제 트리거는 TRUNCATE TABLE 작업이 기록되지 않고 행을 삭제하지 않기 때문에 실행되지 않습니다.
특정 이름을 가진 사용자의 삭제를 방지하는 트리거를 생성해 보겠습니다. 이러한 트리거의 예는 목록 3.6에서 볼 수 있습니다.
목록 3.6. 트리거를 사용하여 삭제를 방지하는 예
CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM 삭제된 WHERE vcName="rrp") BEGIN PRINT "오류, 이 사용자는 삭제할 수 없습니다." ROLLBACK TRANSACTION END
이 예에서는 삭제된 테이블에 "rlr"이라는 항목이 있는지 확인한 다음 삭제를 롤백합니다. "rlr"이라는 테이블에 항목을 추가하고 삭제를 시도하십시오. 응답으로 오류가 표시되어야 합니다.
여러 레코드를 삭제하려고 하면 어떻게 됩니까? 예를 들어 다음 예에서는 두 항목을 삭제합니다.
tbPeoples WHERE vcName="rr" 또는 vcName="BASSIL"에서 삭제
금지가 "rlr"이라는 이름에만 있고 Vasily가 트리거에서 충돌을 일으키지 않더라도 삭제되지 않습니다. 전체 거래가 취소됩니다.
대표이사 해임이 금지된 또 다른 예를 살펴보자. 이것은 트리거 없이는 할 수 없습니다:
CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM 삭제된 WHERE idPosition=1) BEGIN PRINT "오류, 이 사용자는 삭제할 수 없습니다." ROLLBACK TRANSACTION END
이 예에서 "idPosition" 필드가 1이면 항목 삭제가 금지됩니다. 다음과 같이 항목을 삭제해 보십시오.
tbPeoples WHERE idPosition=1에서 삭제
가장 흥미로운 점은 트리거가 아니라 외래 키 제약 조건의 오류가 표시된다는 것입니다. 대표이사는 전화번호를 가지고 있으며, 외부 연결이 있는 경우 기록을 삭제할 수 없으며, 그렇지 않으면 무결성이 침해됩니다. 즉, 모든 CHECK 제약 조건과 외래 키가 확인된 후에 트리거가 실행됩니다. 제한 사항이 더 빨리 작동하고 먼저 확인하는 것이 바람직하기 때문에 매우 논리적입니다. 만약 빠른 확인부정적인 결과를 줄 것입니다. 트리거에서 더 복잡한 검사를 수행하는 이유는 무엇입니까?
이것은 삭제 트리거뿐만 아니라 변경 및 삽입에도 적용됩니다. 실제로 실행 순서를 표시하는 것이 편리한 흥미로운 쿼리를 발견했기 때문에 방금 예제를 고려했습니다.
업데이트 트리거
업데이트는 삭제와 삽입의 두 단계로 이루어집니다. 아니요, 물리적으로 데이터베이스에 변경 사항이 있습니다. 이 트리거는 두 단계를 봅니다. 따라서 기존 행은 삭제된 테이블(즉, 이전 내용)에 배치되고 새 데이터는 삽입된 테이블에 배치됩니다. 트리거는 이러한 테이블을 확인하여 변경될 수 있는 행과 변경 방법을 결정할 수 있습니다.
IF UPDATE 옵션을 지정하여 특정 필드의 업데이트를 모니터링하는 트리거를 선언할 수 있습니다. 이렇게 하면 트리거가 특정 열의 활동을 분리할 수 있습니다. 특정 열에 대한 업데이트가 감지되면 트리거는 열을 업데이트할 수 없다는 오류 메시지를 표시하는 등의 특정 작업을 수행할 수 있습니다.
"vcName" 필드가 변경되면 메시지를 표시할 트리거를 tbPeoples 테이블에 생성해 보겠습니다.
CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT "올바른 이름을 입력했으면 합니다."
IF UPDATE 문 뒤에 괄호 안에 필드가 변경되었는지 확인해야 하는 필드가 있습니다. 그렇다면 검사 다음 문이 실행됩니다. 이 경우 다음과 같은 메시지가 표시됩니다. 인쇄를 사용하여. 지정된 필드가 변경되지 않으면 문장은 물론 실행되지 않습니다. 여러 명령문을 실행해야 하는 경우 BEGIN 및 END로 결합하십시오.
다음 쿼리는 트리거를 테스트합니다.
업데이트 tbPeoples SET vcName="IVANUSHKA" WHERE vcFamil="POCHECHKIN"
트리거의 메시지가 표시되는지 확인하십시오.
트리거를 사용하여 전체 이름을 구성하는 필드("vcFamil", "vcName" 및 "vcSurName")를 변경하는 것을 금지해 보겠습니다. 이를 위해 이러한 필드 중 하나가 변경되면 화면에 금지에 대한 메시지를 표시하고 트랜잭션을 롤백합니다.
CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE(vcName) 또는 업데이트(vcFamil) 또는 업데이트(vcSurname) BEGIN PRINT
이와 같은 쿼리를 사용하면 여러 필드에 대한 업데이트를 한 번에 확인하고 여러 명령문을 출력하는 방법을 쉽게 알 수 있습니다. 확인을 수행하는 것은 IF UPDATE 문이 아니라 UPDATE 문이라는 점에 유의하십시오. SQL Server 개발자가 이 두 문장을 결합하는 이유도 모르겠습니다. 첫 번째는 논리 연산자이고 두 번째는 필드가 업데이트되었는지 확인하는 검사입니다.
3.4.6. 대신에
테이블 및 뷰어에 INSTEAD OF 트리거를 지정할 수 있습니다. 이러한 트리거의 작업은 트리거를 생성한 명령문 대신 실행됩니다. 불분명? 예를 들어보겠습니다. 테이블 업데이트 이벤트에 대한 INSTEAD OF 트리거가 있다고 가정해 보겠습니다. 사용자가 업데이트를 수행하면 트리거가 실행되지만 사용자가 실행한 명령문은 이벤트만 생성합니다. 데이터의 실제 업데이트는 트리거 문의 도움으로 이루어져야 합니다.
각 테이블 또는 뷰어는 이벤트당 하나의 INSTEAD OF 트리거로 제한됩니다. CHECK OPTIONS가 활성화된 뷰어에서는 INSTEAD OF 트리거를 생성할 수 없습니다.
대신 어떻게 사용할 수 있습니까? 두 테이블에서 데이터를 선택하는 뷰 개체가 있다고 가정해 보겠습니다. 이미 알고 있듯이 뷰 데이터는 모두 동일한 테이블에 속하는 경우에만 수정할 수 있습니다. 그러나 트리거의 도움으로 원하는 수의 테이블을 업데이트할 수 있습니다.
직원의 성과 직함을 선택하는 뷰 개체를 만들어 보겠습니다. 이 뷰 개체를 People이라고 부르겠습니다.
CREATE VIEW People as SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition
이제 이 뷰 개체에 대한 INSTEAD OF 트리거를 만들어 보겠습니다. 이 트리거를 사용하면 레코드를 추가할 수 있고 동시에 각각의 테이블에 올바르게 등록됩니다.
목록 3.7. INSTEAD OF 트리거는 데이터를 삽입합니다.
CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- 위치 추가 INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM 삽입 i -- 작업자 추가 INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, 삽입된 idPos i.vcPositionName=pn.vcPositionName 끝
이 예에서 흥미로운 것은 두 번째 줄에서 바로 시작됩니다. 이것은 INSTEAD OF 문과 응답할 이벤트를 지정합니다. 이 경우 이벤트는 삽입(INSERT)입니다.
트리거 코드로 직원의 위치와 직원 자체를 추가하는 두 가지 SQL 쿼리를 실행합니다. 첫 번째 쿼리는 삽입된 테이블에서 모든 작업 이름을 선택하고 tbPosition 테이블에 삽입하는 것만 큼 간단하기 때문에 매우 간단합니다. 그러나 두 번째 요청에서는 직원의 성을 삽입하는 것 외에도 직위를 찾고 연결을 설정해야 합니다. 그렇지 않으면 이러한 복잡한 사기를 시작할 의미가 없습니다. 이 문제를 해결하는 방법은 다음과 같습니다.
INSERT INTO tbPeoples(vcFamil, idPosition) SELECT vcFamil, idPosition FROM 삽입 i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName
보기 개체에 레코드를 추가하려면 다음 쿼리를 시도하십시오.
INSERT INTO People VALUES("IVANUSHKIN", "Clerk")
다음 쿼리를 실행하고 다음을 확인하십시오. 새로운 항목추가:
사람에서 * 선택
테이블을 업데이트할 때 한 가지 문제가 있습니다. 업데이트된 데이터를 기존 데이터와 연결해야 합니다. 가장 먼저 떠오르는 요청은 다음과 같습니다.
업데이트 tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, i WHERE i.vcPositionName = pn.vcPositionName 삽입
여기서 우리는 위치 테이블을 삽입된 테이블에 연결합니다. 그러나 그러한 요청은 결코 이루어지지 않을 것입니다. 왜요? Inserted에는 새 값이 포함되고 tbPosition에는 이전 값이 포함되며 직함은 연결되지 않습니다. 삭제된 테이블에 연결하면 레코드가 연결되지만 테이블에 입력해야 하는 새 값은 알 수 없습니다. 문제는 해결할 수 있지만 가장 좋은 방법은 뷰 개체에 키 필드를 추가하는 것입니다.
ALTER VIEW People as SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition
이제 데이터 업데이트를 위한 INSTEAD OF 트리거는 Listing 3-8과 같습니다.
목록 3.8. 트리거로 연결된 뷰 업데이트
CREATE TRIGGER u_People ON dbo.People 업데이트 대신 tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn 삽입, i WHERE i.idPosition=pn.idPosition 업데이트 tbPeoples SET vcFamil=i.vcFamil 삽입 .idPeoples=pl.idPeoples END
데이터를 업데이트하기 전에 이 섹션에서 생성한 모든 트리거를 삭제하는 것이 좋습니다. 그렇지 않으면 업데이트를 금지하는 여러 트리거를 생성했기 때문에 문제가 발생할 수 있습니다. 따라서 점원을 CEO로 만드십시오.
업데이트 사람 SET vcFamil="IVANUSHKIN", vcPositionName="CEO" WHERE idPeoples=40 및 idPosition=13
한 직원의 직위를 업데이트하면 이 직위의 직원에 대한 직위가 변경되기 때문에 이러한 업데이트는 이상적이지 않습니다. 핸드북은 매우 신중하게 편집해야 합니다.
트리거를 요청하는 사용자는 테이블의 모든 명령문을 실행할 수 있는 권한도 있어야 합니다. 따라서 직원 테이블과 직위 테이블을 업데이트할 수 있는 권한이 있어야 합니다.
3.4.7. 트리거에 대한 추가 정보
트리거를 사용하여 다음을 통해 복잡한 참조 무결성을 적용할 수 있습니다.
- 작업 수행 또는 계단식 업데이트 또는 삭제. CREATE TABLE 문에서 FOREIGN KEY 및 REFERENCE 제약 조건을 사용할 때 참조 무결성이 다를 수 있습니다. 그러나 트리거는 트리거가 더 강력하기 때문에 계단식 삭제 또는 업데이트를 수행해야 할 때 필요한 조치를 취하는 데 유용합니다. 트리거된 테이블에 제약 조건이 있으면 트리거가 실행되기 전에 확인됩니다. 제약 조건을 위반하면 트리거가 작동하지 않습니다. 제한이 작동하지 않으면 트리거를 사용하여 데이터가 무결성을 위반하지 않고 사용자가 허용된 데이터만 입력하도록 하는 보다 복잡한 검사를 구현할 수 있습니다.
- 한 번에 여러 행을 테이블에 삽입할 수 있다는 점을 고려해야 합니다. INSTEAD OF를 사용하여 예제를 만들 때와 마찬가지로 트리거를 작성할 때 이것을 고려해야 합니다.
- 제약 조건, 규칙 및 기본값은 표준에 의해서만 생성될 수 있습니다. 시스템 오류. 사용자 정의 메시지가 필요한 경우 트리거를 사용해야 합니다.
트리거를 디자인할 때 테이블에는 주어진 작업에 대해 여러 트리거가 있을 수 있다는 점을 염두에 두어야 합니다. 각 트리거는 여러 작업 또는 하나의 작업에 대해 선언될 수 있습니다. 예를 들어 다음 예제에서는 두 개의 INSERT 및 UPDATE 이벤트를 처리합니다.
CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples 삽입, 작업으로 업데이트
여러 트리거가 하나의 작업에 할당되어 이름이 충돌하지 않는 경우 수행할 작업 또는 할당을 설명하는 단어를 이름에 추가할 수 있습니다.
테이블 소유자는 첫 번째 및 마지막 트리거를 지정할 수 있습니다. 여러 트리거가 테이블에 배치되면 소유자는 sp_settriggerorder(다음 장에서 저장된 시스템 테이블에 대해 설명함)를 사용하여 실행할 첫 번째 트리거와 마지막 트리거를 지정할 수 있습니다. 다른 트리거의 순서는 설정할 수 없습니다.
테이블 소유자는 뷰어 및 임시 테이블에 대한 트리거를 생성할 수 없습니다. 그러나 트리거는 뷰어 및 임시 테이블을 참조할 수 있습니다. PRINT 문을 사용하여 무언가를 인쇄하는 것이 금지된 것은 아니지만 트리거는 결과 집합을 반환해서는 안 됩니다. 그러나 트랜잭션이 롤백될 때만 사용자가 이를 볼 수 있다는 점에 유의해야 합니다. 따라서 대부분의 경우 이것으로 충분하지만 오류만 보고할 수 있지만 성공적인 실행은 보고할 수 없습니다.
이제 트리거의 성능에 대해 이야기해 보겠습니다. 다음과 같은 이유로 충분히 빠릅니다.
- 트리거 코드 자체에 네트워크 호출이 없으면 서버에 있으며 실행을 위해 네트워크 호출이 필요하지 않습니다.
- Insert 및 Deleted 테이블은 캐시에 있으므로 많은 행을 포함하고 테이블 액세스에 다른 테이블과의 복잡한 관계가 포함되지 않는 한 액세스가 매우 빠릅니다.
필요한 경우에만 트리거를 사용하십시오. 주요 무결성 작업을 제약 조건에 두십시오. 다른 방법이 없다면 서버 성능을 향상시키기 위해 가능한 한 간단하게 트리거 문 선언을 유지하십시오. 트리거는 트랜잭션의 일부이므로 트랜잭션이 완료될 때까지 잠금이 유지되므로 여기서 처리 속도가 가장 중요합니다.
3.4.8. 방아쇠 연습
지식을 통합하는 동시에 미래에 유용할 수 있는 기성 솔루션을 보기 위해 몇 가지 예를 살펴보겠습니다.
데이터베이스에서 매우 자주 기록을 저장해야 합니다. 변경 사항을 저장하기 위해 많은 사람들이 별도의 테이블을 선택합니다. 무엇 때문에? 기본 테이블에는 최신 데이터만 포함되며 최소 크기를 사용하므로 가능한 한 빨리 실행됩니다. 기록은 별도의 테이블에 있으며 별도의 테이블에 저장할 수도 있습니다. 파일 그룹, 데이터를 예약할 때 매우 강력한 기능을 제공합니다.
따라서 tbPeoples 테이블의 행이 변경되거나 삭제되면 tbPeoplesHistory 테이블에 복사하는 트리거를 생성해 보겠습니다. 기본 키가 고유 식별자 형식인 경우 문제는 다음과 같이 해결됩니다.
CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples for update, DELETE INTO INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM 삭제됨 del
tbPeoplesHistory 테이블은 tbPeoples 테이블을 일대일로 반복하지만 자체 기본 키를 가지고 있습니다. 즉, tbPeoples 구조의 시작 부분에 하나의 필드를 추가했습니다. 왜, 언제 기본 테이블의 기본 키 필드를 사용할 수 있습니까? 사실 이 키는 그대로 두는 것이 가장 좋으므로 언제든지 데이터베이스의 다른 테이블과의 기록에서 레코드의 연결을 복원할 수 있습니다.
이 예에서는 Deleted 테이블의 내용을 tbPeoplesHistory 테이블에 복사한다. newid() 함수를 사용하여 기본 키를 생성할 수 있으므로 쿼리가 단순화됩니다.
그러나 우리 작업에서 기본 키는 자동으로 증가하며 생성할 수 없습니다. 모든 필드를 나열해야 합니다.
CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples for update, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM 삭제된 del
이제 두 줄 이상의 삭제를 방지하는 방법을 살펴보겠습니다.
CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM 삭제됨)>1 BEGIN PRINT "두 개 이상의 행을 삭제할 수 없습니다." ROLLBACK TRANSACTION END
모든 트리거에는 변경 기록 예에서 발생한 것처럼 다른 테이블에 영향을 주는 UPDATE, INSERT 또는 DELETE 문이 포함될 수 있습니다. 중첩이 활성화되면 테이블을 수정하는 트리거가 (자체 트리거가 있는 다른 테이블에서 업데이트 작업을 수행하여) 세 번째 트리거를 차례로 실행할 수 있는 다른 트리거를 활성화할 수 있습니다.
중첩은 설치 시 활성화되지만 sp_configure 시스템 절차를 사용하여 비활성화하고 다시 활성화할 수 있습니다. 예를 들어 다음 예제에서는 중첩 트리거를 비활성화합니다.
sp_configure '중첩 트리거', 0
트리거는 최대 32레벨까지 첨부 파일을 가질 수 있습니다. 트리거 루프가 있는 경우 제한이 초과됩니다. 트리거가 중단되고 트랜잭션이 롤백됩니다.
중첩된 트리거는 동일한 트랜잭션에서 두 번 호출되지 않으며 동일한 테이블에 대한 두 번째 업데이트에 대한 응답으로 트리거가 자체적으로 호출되지도 않습니다. 예를 들어 트리거가 원래 테이블을 체인으로 변경하는 테이블을 변경하면 트리거가 다시 호출되지 않습니다.
레벨 중 하나에서 오류가 발생하면 모든 데이터 변경 사항이 롤백됩니다. 모든 중첩 트리거는 하나의 트랜잭션으로 처리됩니다. 즉, ROLLBACK TRANSACTION을 실행하는 동안 변경 사항이 저장되지 않습니다.
중첩 트리거는 복잡하고 잘 계획된 디자인이 필요하기 때문에 개발하기 어렵습니다. 계단식 변경은 영향을 미치고 싶지 않은 데이터를 변경할 수 있습니다. 이것이 때때로 중첩 트리거를 비활성화하는 것이 더 쉬운 이유입니다.
모든 트리거는 다른 테이블이나 동일한 테이블에 영향을 줄 수 있습니다. 재귀 호출 옵션이 활성화되면 테이블의 데이터를 변경하는 트리거가 다시 자체적으로 실행될 수 있습니다. 기본적으로 이 옵션은 데이터베이스가 생성될 때 비활성화됩니다. ALTER DATABASE 문으로 이 옵션을 활성화할 수 있습니다. 재귀 트리거 활성화의 예:
ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON
중첩 옵션이 비활성화되면 재귀도 비활성화되며 항상 기억해야 합니다.
트리거를 사용하여 재귀 삭제를 생성할 수도 있습니다. 예를 들어 다음 예에서는 테이블에 직원이 없는 번호는 단순히 필요하지 않기 때문에 직원이 삭제될 때 전화번호도 삭제되는 트리거를 만듭니다.
CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples for DELETE as DELETE as DELETE as DELETE AS tbPhoneNumbers pn, 삽입 i WHERE pn.idPeoples=i.idPeoples
트리거를 사용하여 특정 계산을 수행할 수 있습니다. tbPeoples 테이블에 전화번호를 저장하는 필드가 있어야 한다고 가정해 보겠습니다. 물론 이것은 숫자를 항상 셀 수 있기 때문에 데이터의 비정규화이지만 이것이 예라는 것을 잊지 마십시오. 필드를 지원하기 위해 다음 트리거를 만들 수 있습니다.
- 전화 테이블에 항목을 추가할 때 직원 테이블의 필드 값을 늘립니다.
- 전화번호를 삭제할 때 필드 값을 줄입니다.
지식을 통합하고 SQL 쿼리 작업을 연습하기 위해 직접 구현해 보십시오.
트리거가 있는 테이블을 정의하려면 sp_depends를 실행합니다. 예를 들어 다음 명령을 실행하여 tbPeoples 테이블에 대한 모든 종속성을 확인합니다.
EXEC sp_depends "tbPeoples"
특정 테이블에 어떤 트리거가 있는지 확인하려면 sp_helptrigger를 실행합니다. 다음 예는 사람 보기 개체에 속한 모든 트리거를 표시합니다(테이블의 트리거를 보려면 해당 이름 지정).
EXEC sp_helptrigger 사람
기존 트리거의 코드를 보려면 sp_helptext를 사용하십시오. 예를 들어 다음 명령을 사용하면 보기 개체에 대해 만든 u_People 트리거의 텍스트를 볼 수 있습니다.
방아쇠 :<Определение_триггера>::= (CREATE | ALTER) TRIGGER trigger_name ON (table_name | view_name ) ( ( ( FOR | AFTER | INSTEAD OF ) ( [ DELETE] [,] [ INSERT] [,] [ UPDATE] ) [ WITH APPEND ] [ NOT FOR 복제 ] AS sql_statement[...n] ) | ( ((FOR | AFTER | INSTEAD OF ) ( [,] ) [ WITH APPEND] [ NOT FOR REPLICATION] AS ( IF UPDATE(column_name) [ (AND | OR) UPDATE( column_name)] [...n] |IF (COLUMNS_UPDATES()(process_bit_operator) change_mask) (comparison_bit_operator )bit_mask [...n]) sql_operator [...n] ) )
트리거는 현재 데이터베이스에서만 만들 수 있지만 원격 서버에 있는 데이터베이스를 포함하여 트리거 내부에서 다른 데이터베이스에 액세스할 수 있습니다.
CREATE | 변경 트리거 .
트리거 이름은 데이터베이스 내에서 고유해야 합니다. 선택적으로 소유자의 이름을 지정할 수 있습니다.
WITH ENCRYPTION 인수를 지정하면 서버가 트리거 코드를 암호화하여 관리자를 포함하여 누구도 액세스하거나 읽을 수 없도록 합니다. 암호화는 종종 프로그래머의 지적 재산이나 영업 비밀인 작성자의 데이터 처리 알고리즘을 숨기는 데 사용됩니다.
트리거 유형
트리거의 동작을 결정하는 SQL Server에는 두 가지 옵션이 있습니다.
- 후에. 트리거는 호출한 명령이 성공적으로 실행된 후 실행됩니다. 어떤 이유로든 명령을 성공적으로 완료할 수 없으면 트리거가 실행되지 않습니다. 사용자 요청 실행 및 트리거 실행의 결과로 데이터 변경이 한 트랜잭션의 본문에서 수행된다는 점에 유의해야 합니다. 트리거가 롤백되면 사용자 변경도 거부됩니다. 각 작업(INSERT, UPDATE, DELETE)에 대해 여러 AFTER 트리거를 정의할 수 있습니다. 테이블에 AFTER 트리거가 여러 개 있는 경우 sp_settriggerorder 시스템 저장 프로시저를 사용하여 먼저 실행할 트리거와 마지막 트리거를 지정할 수 있습니다. 기본적으로 SQL Server의 모든 트리거는 AFTER 트리거입니다.
- 대신에 . 명령을 실행하는 대신 트리거가 호출됩니다. AFTER 트리거와 달리 INSTEAD OF 트리거는 테이블과 뷰 모두에서 정의할 수 있습니다. 각 INSERT , UPDATE , DELETE 작업에 대해 하나의 INSTEAD OF 트리거만 정의할 수 있습니다.
트리거는 응답하는 명령 유형에 따라 구별됩니다.
트리거에는 세 가지 유형이 있습니다.
- INSERT TRIGGER - INSERT 명령을 사용하여 데이터를 삽입하려고 할 때 발생합니다.
- UPDATE TRIGGER - UPDATE 명령을 사용하여 데이터를 변경하려고 할 때 발생합니다.
- DELETE TRIGGER - DELETE 명령을 사용하여 데이터를 삭제하려고 할 때 발생합니다.
건설 [삭제][,][삽입][,][업데이트]그리고 를 위해 | 후 | 대신에 ) ( [,]트리거가 응답할 명령을 결정합니다. 명령을 작성할 때 하나 이상의 명령을 지정해야 합니다. 허용된 트리거 생성, 두 개 또는 세 개의 명령 모두에 응답합니다.
WITH APPEND 인수를 사용하면 각 유형의 여러 트리거를 만들 수 있습니다.
~에 트리거 생성 NOT FOR REPLICATION 인수를 사용하면 복제 메커니즘에 의해 테이블이 수정되는 동안 실행이 금지됩니다.
AS sql_operator[...n] 구성은 트리거가 실행될 때 실행될 SQL 문 및 명령 집합을 정의합니다.
다음과 같은 여러 작업은 트리거 내에서 허용되지 않습니다.
- 데이터베이스 생성, 수정 및 삭제;
- 데이터베이스 또는 트랜잭션 로그 백업을 복원합니다.
이러한 명령은 트리거를 실행하는 트랜잭션이 롤백되는 경우 실행 취소할 수 없기 때문에 허용되지 않습니다. 이 금지는 어떤 식으로든 생성된 트리거의 기능에 영향을 미치지 않을 것입니다. 예를 들어 테이블 행을 변경한 후 트랜잭션 로그의 백업 복사본을 복원해야 하는 경우 이러한 상황을 찾기가 어렵습니다.
트리거 프로그래밍
레코드 추가, 수정 및 삭제 명령을 실행할 때 서버는 두 개의 특수 테이블을 생성합니다. 삽입그리고 삭제. 여기에는 트랜잭션이 끝날 때 삽입되거나 제거될 행 목록이 포함됩니다. 삽입 및 삭제된 테이블의 구조는 트리거가 정의된 테이블의 구조와 동일합니다. 각 트리거는 자체적으로 삽입 및 삭제된 테이블 세트를 생성하므로 다른 트리거가 액세스할 수 없습니다. 트리거를 실행한 작업 유형에 따라 삽입된 테이블과 삭제된 테이블의 내용이 다를 수 있습니다.
- INSERT 명령 - 삽입된 테이블에는 사용자가 테이블에 삽입하려는 모든 행이 포함됩니다. 삭제된 테이블에는 행이 없습니다. 트리거가 완료되면 삽입된 테이블의 모든 행이 소스 테이블로 이동됩니다.
- DELETE 명령 - 삭제된 테이블에는 사용자가 삭제하려고 하는 모든 행이 포함됩니다. 트리거는 각 행을 확인하고 삭제 허용 여부를 결정할 수 있습니다. 삽입된 테이블에는 행이 없습니다.
- UPDATE 명령 - 실행될 때 삭제된 테이블에는 성공적인 완료 시 삭제될 이전 행 값이 포함됩니다.
이미 인터넷에 sql 트리거에 대한 많은 기사가 있지만 "알고 있는" 사람들을 위한 자료를 통합하고 방금 시작한 사람들을 위한 자료를 더 잘 이해하기 위해 적절한 예와 함께 하나 더 추가하겠습니다. "sql의 zen"을 이해합니다. 동시에 주제에 대한 토론을 만들 것입니다.
내 의견은 내 의견일 뿐이며 때로는 매우 범주적이라는 것을 즉시 말해야 합니다. 여러 가지 이유로 로드가 많은 사이트와 복잡한 웹 응용 프로그램으로 작업해야 합니다.
우선 순위와 통계를 모니터링하기 위해 작업을 통해 한 가지 귀중한 경험을 배웠습니다. 무슨 뜻인가요? 간단합니다. 블로그가 있고 하루에 2-3-4-10012백만 명의 방문자가 있고 기사가 하루에 1-2-3-3435번만 작성되는 경우(조회 수보다 10배 적음) , 그러면 기사를 표시하는 속도에 비해 기사를 저장하는 속도(및 그것의 복잡성)가 비례적으로 더 적을 수 있습니다. 우리가 더 많이 보여줄수록 기사/페이지/테이블의 보존이 아니라 디스플레이가 더 중요합니다. 그렇다고 해서 긴장을 풀 수 있는 것은 아닙니다. 블로그에 글을 3~5~10초 만에 저장하는 것은 적정 범위 내이지만, 2초 이상(+ 사진이 있는 스크립트와 스타일이 로드될 때까지)에 페이지를 생성하는 것은 "얼마나 느리다. 사이트, 나는 다른 것을 읽을 것입니다." 그리고 더 나쁜 것은 "나는 다른 곳에서 그것을 사러 갈 것입니다."
설문 조사/카르마, 댓글, 페이지 뷰 카운터 등이 있는 평균적인 사이트를 취하면 많은 개발자가 즉시 SELECT count(*) FROM comment WHERE comment.page=page_id와 같은 구성을 떠올립니다. 글쎄, 각 기사에 대해 평가의 합계, 댓글의 합계를 계산한다고 생각하십시오. 그리고, 우리는 메인 페이지의 각 섹션에서 10개의 기사를 가지고 있습니다. 초당 10명이 참석하면 평균 VPS에서 페이지당 60-100개의 쿼리를 SQL에 처리할 수 있습니다(안녕하세요, Bitrix).
그러나 가사와 함께 지옥에 (아마도 이미 얻었습니다). 베어 데이터:
블로그 테이블
CREATE TABLE IF NOT EXISTS `blog` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL , `img` varchar(128) NOT NULL DEFAULT "default.png", `status` tinyint(4) NOT NULL DEFAULT "2", `user_id` int(11) NOT NULL, `rate' int(11) NOT NULL , `relax_type` tinyint(4) NOT NULL, `timers` 타임스탬프 NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NOT NULL DEFAULT "0", `views` int(11) NOT NULL DEFAULT "0", `comment ` int(11) NOT NULL, `url` varchar(128) NOT NULL, PRIMARY KEY(`id`), UNIQUE KEY `url`(`url`), KEY `country_id`(`country_id`), KEY `user_id ` (`user_id`), KEY `status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;
코멘트 테이블
CREATE TABLE IF NOT EXISTS `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` 텍스트, `creation` 타임스탬프 NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", PRIMARY KEY(`id`), KEY `owner_name`( `owner_name`,`owner_id`), KEY `parent_id`(`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;
보시다시피 블로그 테이블에서 각 기사에는 댓글 카운터(댓글 필드)가 있습니다.
간단한 연습:
1. 댓글 추가 - 블로그 카운터 증가
2. 댓글 삭제/숨김 - 카운터를 줄였습니다.
코드에서 이 작업을 수행하는 것은 편리하고 친숙하지만 더 편리한 도구인 트리거가 있습니다.
따라서 2개의 이벤트(실제로는 3개)가 있습니다. 댓글 작성 및 삭제(세 번째 이벤트는 상태 변경("삭제", 차단 등)입니다.
생성과 삭제만 고려하고 상태 변경은 숙제로 둡니다.
이 예제에는 한 가지 기능이 있습니다. 댓글은 여러 유형의 기사에 대한 것일 수 있습니다.
댓글 작성:
CREATE TRIGGER `add_count_comment` 각 행 시작에 대해 `comments`에 삽입 후 개인 계정그가 쓴 댓글의 수를 세십시오. UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // 주석이 무엇을 참조하는지 확인하고 이 테이블의 카운터를 즉시 증가 CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `블로그 `.id = NEW.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; 종료 사례; // 여기에서 우리는 뉴스 피드 작업을 더 쉽게 할 수 있습니다. // 기사의 URL을 즉시 작성하여 불필요한 CASE NEW를 선택할 필요가 없습니다.`owner_name` WHEN "Blog" THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`. id=NEW.`owner_id`); WHEN "기사" THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; 종료 사례; // THEN CASE NEW.`owner_name`을 선택하지 않도록 기사 제목을 즉시 작성합니다. WHEN "Blog" THEN SET usertitle = ('blog'에서 제목 선택(여기서 blog.id=NEW.`owner_id`)); WHEN "기사" THEN SET usertitle = ('기사'에서 제목 선택, 여기서 article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET usertitle = ` `; 종료 사례; INSERT INTO user_has_events VALUES(NEW.user_id,NEW.id,"Comments",NOW(),userurl, usertitle); 끝
마찬가지로 댓글 삭제:
CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; 종료 사례; 끝
그래서 우리가 얻은 것:
1. 댓글을 삽입할 때 특정 댓글 개체(기사, 페이지, 메모)에 대한 댓글의 합계를 SQL Server를 통해 자동으로 계산했습니다.
2. 우리는 뉴스 피드를 구성했습니다(모든 소셜 네트워크 등).
3. 댓글 삭제 시 모든 데이터를 차감합니다.
4. 프레임워크 도구를 사용하지 않았습니다.
5. 필요한 모든 데이터의 선택이 빠릅니다(페이지가 표시될 때 1번의 요청만 가능하며 다른 "왼쪽" 데이터는 예외입니다.)
그리고 마지막 순간에 변경된 기사를 주기적으로 선택하는 스핑크스도 있습니다. 이를 위해 블로그에 수정 필드가 있습니다.
추가된 트리거:
CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // 데이터를 "대체"하여 정보를 저장하기 전에 시간을 삽입합니다. 각 행 시작 설정 NEW.modification = NOW(); 끝
이제 마지막 순간을 선택하면 마지막 순간에 추가된 모든 문서가 표시됩니다.
CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // 데이터를 "대체"하여 정보를 저장하기 전에 시간을 삽입합니다. 각 행 시작 설정 NEW.modification = NOW(); 끝
데이터가 변경되면 검색 색인도 업데이트됩니다.
일반적으로 일반 프로젝트에서는 SQL Server 측으로 전송할 수 있는 모든 것이 전송됩니다. SQL Server 자체는 사용되는 프로그래밍 언어를 통해 수행할 수 있는 것보다 더 적은 리소스로 이러한 작업을 더 빠르게 수행합니다.
UPD: 데이터베이스 구조를 복잡하게 만드는 편의에 전념하는 Holivar가 공개되었습니다.
333 163
위의 구문은 DML 트리거에만 적용됩니다. DDL 트리거는 약간 다른 형태의 구문을 가지고 있으며 나중에 보여드리겠습니다.
여기서 schema_name 매개변수는 트리거가 속한 스키마의 이름을 지정하고, trigger_name 매개변수는 트리거의 이름을 지정합니다. table_name 매개변수는 트리거가 생성되는 테이블의 이름을 지정합니다. (view_name 매개변수가 있는 것처럼 보기에 대한 트리거도 지원됩니다.)
두 가지를 사용하여 트리거 유형을 설정할 수도 있습니다. 추가 옵션: 이후 및 대신. ( 매개변수를 위해 AFTER 매개변수와 동의어입니다.) AFTER 유형 트리거트리거를 발생시키는 작업이 실행된 후에 호출되며, INSTEAD OF 유형 트리거트리거를 실행하는 작업 대신 실행됩니다. AFTER 트리거는 테이블에서만 생성할 수 있으며 INSTEAD OF 트리거는 테이블과 뷰 모두에서 생성할 수 있습니다.
INSERT, UPDATE 및 DELETE 매개변수는 트리거 작업을 지정합니다. 트리거 작업은 트리거를 발생시키는 Transact-SQL 문입니다. 이 세 가지 명령의 조합이 허용됩니다. IF UPDATE 옵션을 사용하는 경우 DELETE 문은 허용되지 않습니다.
CREATE TRIGGER 문의 구문에서 볼 수 있듯이 트리거 작업은 AS sql_statement 사양에 지정됩니다.
데이터베이스 엔진을 사용하면 각 테이블과 각 작업(INSERT, UPDATE 및 DELETE)에 대해 여러 트리거를 만들 수 있습니다. 기본적으로 주어진 수정 작업에 대한 여러 트리거에 대한 특정 실행 순서는 없습니다.
데이터베이스 소유자, DDL 관리자 및 트리거가 정의된 테이블의 소유자만 현재 데이터베이스에 대한 트리거를 생성할 수 있습니다. (다른 유형의 CREATE 문의 권한과 달리 이 권한은 전달할 수 없습니다.)
트리거 구조 변경
Transact-SQL 언어는 문도 지원합니다. 변경 트리거, 트리거의 구조를 수정합니다. 이 명령은 일반적으로 트리거 본문을 변경하는 데 사용됩니다. ALTER TRIGGER문의 모든 절 및 매개변수는 동일한 이름의 CREATE TRIGGER문의 절 및 매개변수와 동일한 의미를 갖습니다.
현재 데이터베이스에서 트리거를 제거하려면 다음 명령문을 사용하십시오. 드롭 트리거.
삭제 및 삽입된 가상 테이블 사용
트리거 작업을 생성할 때 일반적으로 트리거를 발생시키는 작업에 의해 변경되기 전 또는 후에 열 값을 참조하는지 여부를 지정해야 합니다. 이러한 이유로 특별히 명명된 두 개의 가상 테이블이 트리거를 발생시키는 명령문의 효과를 테스트하는 데 사용됩니다.
삭제됨 - 테이블에서 삭제된 행의 복사본을 포함합니다.
삽입 - 테이블에 삽입된 행의 복사본을 포함합니다.
이러한 테이블의 구조는 트리거가 정의된 테이블의 구조와 동일합니다.
테이블 삭제됨 DELETE 또는 UPDATE 절이 CREATE TRIGGER 문에 지정된 경우 사용되며 해당 문에 INSERT 또는 UPDATE 절이 지정된 경우 사용됩니다. 삽입된 테이블. 즉, 트리거 작업에서 실행되는 각 DELETE 문에 대해 삭제된 테이블이 생성됩니다. 마찬가지로 트리거 작업에서 실행되는 각 INSERT 문에 대해 삽입된 테이블이 생성됩니다.
UPDATE 문은 DELETE 문 다음에 INSERT 문으로 처리됩니다. 따라서 트리거 작업에서 실행되는 각 UPDATE 문에 대해 삭제된 테이블과 삽입된 테이블이 모두 생성됩니다(순서대로).
삽입 및 삭제된 테이블은 이전 기사에서 설명한 행 버전 관리를 사용하여 구현됩니다. 적절한 트리거가 있는 테이블에서 DML 문(INSERT, UPDATE 또는 DELETE)을 실행하면 해당 테이블에 대한 모든 변경 사항에 대해 항상 행 버전이 생성됩니다. 트리거가 삭제된 테이블의 정보를 필요로 하는 경우 행 버전 저장소의 데이터에 액세스합니다. 삽입된 테이블의 경우 트리거는 가장 최근의 행 버전을 확인합니다.
행 버전 관리 엔진은 tempdb 시스템 데이터베이스를 행 버전 저장소로 사용합니다. 이러한 이유로 데이터베이스에 자주 사용하는 트리거가 많이 포함되어 있으면 이 트리거의 볼륨이 크게 증가할 것으로 예상해야 합니다. 시스템 기반데이터.
DML 트리거의 범위
이러한 트리거는 다양한 문제를 해결하는 데 사용됩니다. 이 섹션에서는 DML 트리거, 특히 AFTER 및 INSTEAD OF 트리거의 여러 용도를 살펴보겠습니다.
트리거 후
이미 알고 있듯이 AFTER 트리거는 트리거를 발생시키는 작업이 실행된 후에 호출됩니다. AFTER 트리거는 AFTER 또는 FOR 키워드를 사용하여 지정됩니다. AFTER 트리거는 기본 테이블에서만 생성할 수 있습니다. 이 유형의 트리거는 특히 다음 작업을 수행하는 데 사용할 수 있습니다.
데이터베이스 테이블에 활동 로그의 로그 생성;
비즈니스 규칙의 구현;
참조 무결성의 시행.
로그 로그 생성
SQL Server에서는 CDC(변경 데이터 캡처) 시스템을 사용하여 데이터 변경 캡처를 수행할 수 있습니다. DML 트리거로 이 문제를 해결할 수도 있습니다. 아래 예는 트리거를 사용하여 데이터베이스 테이블에 활동 로그 로그를 생성하는 방법을 보여줍니다.
샘플 DB를 사용하십시오. /* AuditBudget 테이블은 Project 테이블의 활동 로그로 사용됩니다. */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(예산) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT 예산 FROM 삭제) SELECT @budgetNew = (SELECT 예산 FROM 삽입) SELECT @projectNumber = (SELECT 번호 FROM 삭제됨) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END
이 예에서는 Project 테이블의 예산 열에 대한 변경 사항을 저장하는 AuditBudget 테이블을 만듭니다. 이 열에 대한 변경 사항은 trigger_ModifyBudget을 통해 이 테이블에 기록됩니다.
이 트리거는 UPDATE 문을 사용하여 예산 열이 변경될 때마다 실행됩니다. 이 트리거가 실행되면 삭제 및 삽입된 테이블의 행 값이 해당 @budgetOld, @budgetNew, @projectNumber 변수에 할당됩니다. 이러한 할당된 값은 사용자 이름 및 현재 날짜, 그러면 AuditBudget 테이블에 삽입됩니다.
이 예에서는 한 번에 하나의 행만 업데이트된다고 가정합니다. 따라서 이 예는 트리거가 다중 행 업데이트를 처리하는 일반적인 경우를 단순화한 것입니다. 다음 Transact-SQL 문을 실행하는 경우:
AuditBudget 테이블의 내용은 다음과 같습니다.
비즈니스 규칙 구현
트리거를 사용하여 애플리케이션에 대한 비즈니스 규칙을 생성할 수 있습니다. 이러한 트리거의 생성은 아래 예에 나와 있습니다.
샘플 DB를 사용하십시오. -- trigger_TotalBudget은 트리거를 사용하여 비즈니스 규칙을 구현하는 예입니다. GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE(예산) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM ( 예산) FROM 삽입) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM 프로젝트 p WHERE p.Number NOT IN (SELECT d.Number FROM 삭제 d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM 삭제) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT "예산 변경되지 않음" ROLLBACK TRANSACTION END ELSE PRINT "예산 변경 완료됨" END
여기에서 프로젝트 예산 수정을 제어하는 규칙을 만듭니다. trigger_TotalBudget 트리거는 각 예산 변경을 확인하고 모든 예산의 합계를 50% 이하로 늘리는 UPDATE 문만 실행합니다. 그렇지 않으면 UPDATE 문은 ROLLBACK TRANSACTION 문을 통해 롤백됩니다.
무결성 제약 조건 적용
데이터베이스 관리 시스템은 데이터 무결성을 보장하기 위해 두 가지 유형의 제약 조건을 사용합니다. CREATE TABLE 및 ALTER TABLE 언어 문을 사용하여 정의되는 선언적 제약 조건; 트리거를 통해 구현되는 절차적 무결성 제약 조건.
일반적인 상황에서는 선언적 무결성 제약 조건이 시스템에서 지원되고 사용자가 구현할 필요가 없기 때문에 사용해야 합니다. 선언적 무결성 제약 조건이 없는 경우에만 트리거가 권장됩니다.
다음 예는 Employee 및 Works_on 테이블에서 트리거를 사용하여 참조 무결성을 적용하는 방법을 보여줍니다.
샘플 DB를 사용하십시오. GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, 삽입 WHERE Employee.Id = 삽입.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT "수정된 행"이 삽입/수정되지 않았습니다. END ELSE PRINT "행이 삽입/수정되었습니다" END
이 예에서 trigger_WorksonIntegrity 트리거는 Employee 및 Works_on 테이블에 대한 참조 무결성을 확인합니다. 즉, 참조 테이블 Works_on의 Id 열에 대한 모든 변경 사항이 확인되고 이 제약 조건을 위반하면 작업이 수행되지 않습니다. (새 값을 Id 열에 삽입할 때도 동일하게 적용됩니다.) 두 번째 BEGIN 블록의 ROLLBACK TRANSACTION 문은 참조 무결성 제약 조건을 위반한 경우 INSERT 또는 UPDATE 문을 롤백합니다.
이 예에서 트리거는 Employee 및 Works_on 테이블 간의 첫 번째 및 두 번째 경우 참조 무결성 문제를 확인합니다. 그리고 아래 예는 동일한 테이블 간의 세 번째 및 네 번째 경우에서 참조 무결성 문제를 확인하는 트리거를 보여줍니다(이러한 경우는 Transact-SQL - 테이블 만들기 문서에서 논의됨).
샘플 DB를 사용하십시오. GO CREATE TRIGGER trigger_RefintWorkson2 ON 직원 AFTER DELETE, UPDATE AS IF UPDATE(Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, 삭제 WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT "행이 삽입/수정되지 않았습니다. " END ELSE PRINT "행이 삽입/수정되었습니다." END
트리거 대신
제안 트리거 대신에실행한 해당 작업을 대체합니다. 이 트리거는 삽입 및 삭제된 해당 테이블이 생성된 후 무결성 제약 조건 검사 또는 기타 작업이 수행되기 전에 실행됩니다.
INSTEAD OF 트리거는 테이블과 뷰 모두에서 생성할 수 있습니다. Transact-SQL 문이 INSTEAD OF 트리거가 정의된 뷰를 참조하는 경우 데이터베이스 시스템은 테이블에 대해 작업을 수행하는 대신 해당 트리거를 실행합니다. 이 유형의 트리거는 항상 뷰에 대해 생성된 삽입 및 삭제된 테이블의 정보를 사용하여 요청된 이벤트를 생성하는 데 필요한 명령문을 생성합니다.
INSTEAD OF 트리거가 제공하는 열 값은 특정 요구 사항을 충족해야 합니다.
계산된 열에는 값을 설정할 수 없습니다.
타임스탬프 데이터 유형이 있는 열에는 값을 설정할 수 없습니다.
IDENTITY_INSERT가 ON으로 설정되어 있지 않으면 IDENTITY 속성이 있는 열에 대해 값을 설정할 수 없습니다.
이러한 요구 사항은 기본 테이블을 참조하는 INSERT 및 UPDATE 문에만 유효합니다. INSTEAD OF 트리거가 있는 뷰를 참조하는 INSERT 문은 해당 뷰에서 null을 허용하지 않는 모든 열에 대한 값을 제공해야 합니다. (UPDATE 문에도 동일하게 적용됩니다. INSTEAD OF 트리거가 있는 뷰를 참조하는 UPDATE 문은 SET 절에서 참조하는 nullable이 아닌 뷰의 모든 열에 대한 값을 제공해야 합니다.)
아래 예는 테이블과 해당 뷰를 사용하여 계산된 열에 값을 삽입할 때 동작의 차이를 보여줍니다.
샘플 DB를 사용하십시오. CREATE TABLE Orders(OrderId INT NOT NULL, 가격 MONEY NOT NULL, 수량 INT NOT NULL, OrderDate DATETIME NOT NULL, 총 AS 가격 * 수량, ShippedDate AS DATEADD(DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM 주문; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO 주문 SELECT 주문 ID, 가격, 수량, 주문 날짜 FROM 삽입 END
이 예에서는 두 개의 계산된 열이 포함된 Orders 테이블을 사용합니다. view_AllOrders는 이 테이블의 모든 행을 포함합니다. 이 보기는 보기가 생성되는 기본 테이블의 계산된 열에 매핑되는 해당 열의 값을 설정하는 데 사용됩니다. 이를 통해 INSTEAD OF 트리거를 사용할 수 있습니다. 이 트리거는 INSERT 문의 경우 view_AllOrders 뷰를 통해 기본 테이블에 값을 삽입하는 일괄 처리로 대체됩니다. (기본 테이블에 직접 액세스하는 INSERT 문은 계산 열에 값을 설정할 수 없습니다.)
첫 번째와 마지막 트리거
데이터베이스 엔진을 사용하면 각 테이블이나 뷰에 대해 그리고 이에 대한 각 작업(INSERT, UPDATE 및 DELETE)에 대해 여러 트리거를 만들 수 있습니다. 또한 특정 작업에 대해 정의된 여러 트리거에 대한 실행 순서를 지정할 수 있습니다. 시스템 절차 사용 sp_settriggerorder테이블에 대해 정의된 AFTER 트리거 중 하나가 처리되는 각 작업에 대해 처음 또는 마지막으로 실행되도록 지정할 수 있습니다. 이 시스템 프로시저에는 세 가지 값 중 하나로 설정할 수 있는 @order 매개변수가 있습니다.
첫 번째 - 트리거가 작업을 수정하기 위해 실행된 첫 번째 AFTER 트리거임을 나타냅니다.
last는 이 트리거가 작업을 시작하기 위해 실행되는 마지막 AFTER 트리거임을 나타냅니다.
없음 - 트리거에 대한 특정 실행 순서가 없음을 나타냅니다. (이 값은 일반적으로 트리거의 이전에 설정된 실행 순서를 처음 또는 마지막으로 재설정하는 데 사용됩니다.)
ALTER TRIGGER 문을 사용하여 트리거 구조를 변경하면 트리거의 실행 순서(첫 번째 또는 마지막)가 바뀝니다. sp_settriggerorder 시스템 프로시저의 사용은 아래 예에 나와 있습니다.
샘플 DB를 사용하십시오. EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "첫 번째", @stmttype="업데이트"
테이블에는 처음 하나의 AFTER 트리거와 마지막 AFTER 트리거만 하나만 허용됩니다. 나머지 AFTER 트리거는 지정되지 않은 순서로 실행됩니다. 시스템 프로시저를 사용하여 트리거 실행 순서를 찾을 수 있습니다. sp_helptrigger또는 OBJECTPROPERTY 함수.
sp_helptrigger 시스템 프로시저에서 반환된 결과 집합에는 지정된 트리거가 실행되는 순서를 지정하는 순서 열이 포함되어 있습니다. objectproperty 함수가 호출될 때 두 번째 매개변수는 ExeclsFirstTrigger 또는 ExeclsLastTrigger이고 첫 번째 매개변수는 항상 식별 번호데이터베이스 개체. 두 번째 매개변수에 지정된 속성이 true이면 함수는 1을 반환합니다.
INSTEAD OF 트리거는 테이블이 변경되기 전에 실행되기 때문에 이 유형의 트리거에 대해 "첫 번째" 또는 "마지막" 실행 순서를 지정할 수 없습니다.
DDL 트리거 및 그 용도
이전에 INSERT, UPDATE 또는 DELETE 문에 의해 테이블이 수정될 때 서버에서 수행할 작업을 지정하는 DML 트리거를 살펴보았습니다. 또한 데이터베이스 엔진에서는 CREATE DATABASE, DROP TABLE 및 ALTER TABLE과 같은 DDL 문에 대한 트리거를 정의할 수 있습니다. DDL 문에 대한 트리거의 구문은 다음과 같습니다.
CREATE TRIGGER trigger_name ON (ALL SERVER | DATABASE ) (FOR | AFTER ) ( event_group | event_type | LOGON) AS (batch | EXTERNAL NAME method_name) 구문 규칙
구문에서 알 수 있듯이 DDL 트리거는 DML 트리거와 동일한 방식으로 생성됩니다. 그리고 이러한 트리거를 수정하고 제거하려면 DML 트리거와 동일한 ALTER TRIGGER 및 DROP TRIGGER 문을 사용하십시오. 따라서 이 섹션에서는 DDL 트리거 구문에 새로운 CREATE TRIGGER 옵션에 대해서만 설명합니다.
DDL 트리거를 정의하는 첫 번째 단계는 범위를 지정하는 것입니다. DATABASE 절현재 데이터베이스를 DDL 트리거의 범위로 지정하고, 모든 서버 제공- 현재 서버.
DDL 트리거의 범위를 지정한 후에는 하나 이상의 DDL 문의 실행에 대한 응답으로 트리거가 실행되는 방식을 지정해야 합니다. event_type 매개 변수는 트리거를 발생시키는 DDL 문을 지정하고 대체 event_group 매개 변수는 Transact-SQL 이벤트 그룹을 지정합니다. DDL 트리거는 event_group 매개 변수에 지정된 Transact-SQL 이벤트가 실행된 후 실행됩니다. 예어 로그온로그인 트리거를 지정합니다.
DML과 DDL 트리거 간의 유사점 외에도 몇 가지 차이점이 있습니다. 이 두 종류의 트리거 간의 주요 차이점은 DDL 트리거의 범위를 단순히 전체 데이터베이스 또는 전체 서버로 지정할 수 있다는 것입니다. 별도의 개체. 또한 DDL 트리거는 INSTEAD OF 트리거를 지원하지 않습니다. 짐작할 수 있듯이 DDL 트리거는 테이블의 내용을 변경하지 않기 때문에 삽입 및 삭제된 테이블이 필요하지 않습니다.
다음 하위 섹션에서는 데이터베이스 수준 트리거와 서버 수준 트리거라는 두 가지 형식의 DDL 트리거에 대해 자세히 설명합니다.
데이터베이스 수준 DDL 트리거
다음 예는 범위가 현재 데이터베이스에 있는 DDL 트리거를 구현하는 방법을 보여줍니다.
샘플 DB를 사용하십시오. GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT "트리거를 삭제하기 전에 "trigger_PreventDrop"을 비활성화해야 합니다." ROLLBACK
이 예의 트리거는 사용자가 SampleDb 데이터베이스에 대한 트리거를 삭제하지 못하도록 합니다. DATABASE 절은 trigger_PreventDrop 트리거가 데이터베이스 수준 트리거임을 지정합니다. 예어 DROP_TRIGGER트리거가 삭제되는 것을 방지하는 사전 정의된 이벤트 유형을 지정합니다.
서버 수준 DDL 트리거
서버 수준 트리거는 서버 이벤트에 응답합니다. 서버 수준 트리거는 CREATE TRIGGER 문에서 ALL SERVER 절을 사용하여 생성됩니다. 트리거가 수행하는 작업에 따라 두 가지가 있습니다. 다른 유형서버 수준 트리거: 일반 DDL 트리거 및 로그인 트리거. 일반 DDL은 DDL 문 이벤트를 기반으로 실행되고 로그인 트리거는 로그인 이벤트를 기반으로 실행됩니다.
다음 예는 로그인 트리거인 서버 수준 트리거를 만드는 방법을 보여줍니다.
유스마스터; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE to loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON EXECUTE AS "loginTest" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "loginTest" AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_loginT ROLLBACK =) ; 끝;
여기서 SQL Server loginTest 로그인이 먼저 생성된 다음 서버 수준 트리거에서 사용됩니다. 이러한 이유로 이 로그인에는 GRANT 문을 통해 부여된 VIEW SERVER STATE 권한이 필요합니다. 그 후, trigger_ConnectionLimit이 생성됩니다. 이 트리거는 LOGON 키워드로 표시되는 로그온 트리거입니다.
보기의 도움으로 sys.dm_exec_sessions loginTest 로그인을 사용하여 세션이 이미 설정되었는지 확인합니다. 세션이 이미 설정되어 있으면 ROLLBACK 문이 실행됩니다. 따라서 loginTest 로그인은 한 번에 하나의 세션만 설정할 수 있습니다.
트리거 및 CLR
저장 프로시저 및 사용자 정의 함수와 마찬가지로 트리거는 CLR(공용 언어 런타임)을 사용하여 구현할 수 있습니다. CLR의 트리거는 세 단계로 생성됩니다.
트리거 소스 코드는 C# 또는 비주얼 베이직, 그런 다음 적절한 컴파일러를 사용하여 개체 코드로 컴파일됩니다.
개체 코드는 해당 실행 파일을 생성하기 위해 CREATE ASSEMBLY 문에 의해 처리됩니다.
CREATE TRIGGER 문은 트리거를 생성합니다.
다음 예에서는 CLR 트리거를 만드는 세 단계를 모두 완료하는 방법을 보여줍니다. 아래는 예시입니다 소스 코드기사의 첫 번째 예제에서 트리거에 대한 C# 프로그램. 다음 예제에서 CLR 트리거를 만들기 전에 먼저 trigger_PreventDrop 트리거를 제거한 다음 두 경우 모두 DROP TRIGGER 문을 사용하여 trigger_ModifyBudget 트리거를 제거해야 합니다.
시스템 사용; System.Data.SqlClient를 사용하여; Microsoft.SqlServer.Server 사용 public class Triggers ( public static void ModifyBudget() ( SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // 예산 열 ( float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection) =true"), conn.Open(), SqlCommand cmd = conn.CreateCommand(), cmd.CommandText = "삭제된 예산 선택", budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()), cmd.CommandText = "SELECT 예산 FROM INSERTED", budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()), cmd.CommandText = "SELECT Number FROM DELETED", project_number = Convert.ToString(cmd.ExecuteScalar()), cmd. CommandText = @"INSERT INTO AuditBudget(@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld) ", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); ) ) )
Microsoft.SQLServer.Server 네임스페이스에는 C# 프로그램에 필요할 수 있는 모든 클라이언트 클래스가 포함되어 있습니다. 클래스 SQLTriggerContext그리고 SQL 함수이 네임스페이스의 구성원입니다. 또한 System.Data.SqlClient 네임스페이스에는 클라이언트와 데이터베이스 서버 간의 연결 및 상호 작용을 설정하는 데 사용되는 SqlConnection 및 SqlCommand 클래스가 포함되어 있습니다. 연결 문자열 "context connection = true"를 사용하여 연결이 설정됩니다.
그런 다음 트리거를 구현하는 데 사용되는 Triggers 클래스가 정의됩니다. ModifyBudget() 메서드는 같은 이름의 트리거를 구현합니다. SqlTriggerContext 클래스의 컨텍스트 인스턴스를 사용하면 프로그램이 트리거가 실행될 때 생성된 가상 테이블에 액세스할 수 있습니다. 이 테이블은 트리거를 발생시킨 데이터를 저장합니다. SqlTriggerContext 클래스의 IsUpdatedColumn() 메서드를 사용하면 지정된 테이블 열이 업데이트되었는지 알 수 있습니다.
이 프로그램에는 SqlConnection 및 SqlCommand라는 두 가지 다른 중요한 클래스가 포함되어 있습니다. SqlConnection 클래스의 인스턴스는 일반적으로 데이터베이스에 대한 연결을 설정하는 데 사용되는 반면 SqlCommand 클래스의 인스턴스를 사용하면 SQL 문을 실행할 수 있습니다.
이 예제의 프로그램은 내장된 csc 컴파일러를 사용하여 컴파일할 수 있습니다. 비주얼 스튜디오. 다음 단계는 데이터베이스에서 컴파일된 어셈블리에 대한 참조를 추가하는 것입니다.
샘플 DB를 사용하십시오. GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE
CREATE ASSEMBLY 문은 관리 코드를 입력으로 사용하고 CLR 트리거가 만들어지는 적절한 개체를 만듭니다. 예제의 WITH PERMISSION_SET 절은 액세스 권한이 SAFE로 설정되었음을 나타냅니다.
마지막으로 아래 예에서 CREATE TRIGGER 문을 사용하여 trigger_modify_budget이 생성됩니다.
샘플 DB를 사용하십시오. GO CREATE TRIGGER trigger_modify_budget ON 프로젝트 업데이트 후 외부 이름 CLRStoredProcedures.Triggers.ModifyBudget으로 업데이트
예의 CREATE TRIGGER 문은 다음을 포함한다는 점에서 이전 예의 동일한 문과 다릅니다. 외부 이름 매개변수. 이 옵션은 코드가 CLR에 의해 생성되도록 지정합니다. 이 매개변수의 이름은 세 부분으로 구성됩니다. 첫 번째 부분은 해당 어셈블리의 이름(CLRStoredProcedures)이고, 두 번째 부분은 위의 예제에 정의된 공용 클래스의 이름(Triggers)이고, 세 번째 부분은 해당 클래스에 정의된 메서드의 이름(ModifyBudget)입니다. .
방아쇠데이터베이스는 데이터베이스에 저장된 특별히 명명된 PL/SQL 블록입니다. 각 트리거는 특정 테이블과 연관되며 DML 문(INSERT, DELETE, UPDATE) 중 하나 또는 이들의 조합이 이 테이블에서 실행될 때 ORACLE에 의해 자동으로 시작됩니다.
트리거 할당.트리거를 사용할 수 있습니다.
1) 테이블을 생성할 때 표준 방식으로 구현할 수 없는 복잡한 데이터 무결성 제약을 구현합니다.
2) 부정거래 방지
3) 접근 권한 및 데이터 비밀성을 종합적으로 검증하기 위한 절차의 구현
4) 테이블 열에서 사용 가능한 값을 기반으로 일부 표현식 생성
5) 데이터 처리를 위한 복잡한 비즈니스 규칙의 구현("에코"를 추적하는 기능, 즉 하나의 테이블을 변경할 때 관련된 테이블의 데이터를 업데이트하는 기능).
트리거 생성 및 활성화.생성하고 자동 시작트리거에는 다음과 같은 일반 구문이 적용됩니다.
CREATE TRIGGER 트리거 이름
(전 | 후)
(삽입 | 삭제 | 업데이트 )
ON table_name
< PL/SQL_блок >
OR REPLACE 키워드가 있는 경우 트리거가 이미 있는 경우 트리거가 다시 생성됩니다.
전에 | AFTER는 트리거가 실행된 시간을 나타냅니다. BEFORE 옵션은 활성화된 DML 문이 실행되기 전에 트리거가 실행됨을 의미합니다. AFTER 옵션은 활성화된 DML 문이 실행된 후 트리거가 실행됨을 의미합니다.
삽입 | 삭제 | UPDATE는 트리거를 활성화하는 DML 문의 유형을 지정합니다. 사용 허용 논리 연산또는 활성화 명령문 세트를 지정하십시오(예: INSERT OR DELETE). UPDATE 옵션을 사용할 때 열 목록이 지정되면 지정된 열 중 하나가 업데이트될 때 트리거가 실행됩니다. 열 목록이 없으면 트리거와 연결된 테이블의 열이 변경될 때 트리거가 시작됩니다.
FOR EACH ROW 구문은 트리거 작업의 특성(문자열 또는 연산자)을 나타냅니다. FOR EACH ROW 구문이 있는 경우 트리거는 문자열 트리거입니다. 없는 경우 트리거는 연산자입니다. 명령문 트리거는 트리거와 연관된 테이블에서 수정된 행 수에 관계없이 트리거를 활성화하는 DML 명령문의 실행 전후에 한 번 실행됩니다. 행 트리거는 트리거를 발생시키는 DML 문에 의해 수정된 각 행에 대해 한 번 발생합니다.
WHEN 키워드를 사용하면 트리거와 연결된 테이블의 행에 대한 추가 제한을 설정할 수 있으며 수정 시 트리거가 실행될 수 있습니다.
PL/SQL_block 구성은 트리거가 실행될 때 ORACLE이 실행하는 PL/SQL 블록을 나타냅니다.
방아쇠의 분류.기본적으로 12가지 유형의 트리거가 있습니다. 트리거 유형은 다음 세 가지 매개변수의 조합으로 결정됩니다.
1) 트리거가 관련된 테이블의 행(문자열 또는 연산자)에 미치는 영향의 특성
2) 트리거가 실행되는 순간: 트리거를 활성화하는 DML 문의 실행 전(BEFORE) 또는 후(AFTER)
3) 트리거를 활성화하는 DML 문의 유형(INSERT, DELETE, UPDATE)
트리거 활성화 순서입니다.테이블에 여러 유형의 트리거가 있는 경우 다음 체계에 따라 활성화됩니다.
1) BEFORE 문 트리거가 실행됩니다(여러 개 있는 경우 실행되는 순서에 대해 말할 수 없음).
2) BEFORE 문자열 트리거가 실행됩니다.
3) 트리거를 활성화하는 DML 문이 실행되고 모든 데이터 무결성 제약 조건이 검사됩니다.
4) AFTER 문자열 트리거가 실행된 후 모든 데이터 무결성 제약 조건이 검사됩니다.
5) 명령문 트리거 AFTER가 실행됩니다.
트리거 술어.트리거가 트리거 활성화 DML 문 집합(예: INSERT OR DELETE)을 지정하는 경우 트리거와 연결된 테이블에서 수행되는 특정 DML 문을 인식하기 위해 트리거 조건자가 사용됩니다. INSERTING, DELETING, UPDATING. 활성화 연산자의 유형이 술어의 유형과 같으면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환하는 부울 함수입니다. 조건문에서 서로 다른 DML 문이 실행될 때 동일한 동작을 지정하기 위해 논리 연산을 사용하여 트리거 술어를 결합합니다.
의사 기록.문자열 트리거의 경우 테이블 행에서 DML 연산자를 실행할 때 수정 전에 있던 이전 값과 수정 후 행에 나타날 새 값에 모두 액세스할 수 있는 특수 구성이 있습니다. 이러한 구성을 의사 레코드라고 하며 이전 및 새 것으로 표시됩니다. 이러한 의사 레코드의 구조는 수정된 테이블 행의 구조와 동일하지만 의사 레코드의 개별 필드만 조작할 수 있습니다. Pseudo-record 필드는 다음 방식에 따라 액세스됩니다. old 또는 new 앞에 ":" 기호가 배치되고 필드 이름이 점으로 표시됩니다. DML 문을 활성화할 때 의사 레코드 필드가 취하는 값은 다음과 같이 정의됩니다.
INSERT 문 - :new 의사 레코드는 삽입되는 행과 동일하고 모든 필드의 :old 의사 레코드는 NULL입니다.
DELETE 문 - :old 의사 레코드는 삭제되는 행과 동일하고 모든 필드의 :new 의사 레코드는 NULL입니다.
UPDATE 문 - :new 의사 레코드는 업데이트 결과 문자열과 동일하며 모든 필드의 :old 의사 레코드에는 문자열의 원래 값이 있습니다.
트리거를 활성화, 비활성화합니다.데이터베이스에 저장된 트리거는 데이터베이스에서 제거하지 않고 일시적으로 비활성화할 수 있습니다. 이를 위해 다음 명령이 사용됩니다.
ALTER TRIGGER 트리거 이름 DISABLE;
명령을 사용하여 일정 시간 후에 트리거를 켤 수 있습니다.
ALTER TRIGGER 트리거 이름 ENABLE;
명령을 사용하여 특정 테이블과 관련된 모든 트리거의 시작을 비활성화하거나 허용할 수 있습니다.
ALTER TABLE table_name (DISABLE | ENABLE) 모든 트리거;
여기서 DISABLE은 비활성화하는 데 사용되고 ENABLE은 테이블의 모든 트리거를 활성화하는 데 사용됩니다.
데이터베이스에서 트리거를 제거합니다.트리거 제거, 즉 데이터베이스에서 트리거 삭제는 다음 명령을 사용하여 수행됩니다.
DROP TRIGGER 트리거 이름;
트리거에 대한 정보를 가져옵니다.트리거는 데이터베이스에 저장되므로 트리거에 대한 정보는 예를 들어 다음 명령을 사용하여 USER_TRIGGERS 데이터 사전 보기에서 얻을 수 있습니다.
SELECT * FROM USER_TRIGGERS;
예.
1. KNIGA_POSTAVKA 테이블에 다음 행을 삽입하기 전에 존재 여부를 확인하는 트리거 생성 지정된 코드 KNIGA 테이블에 있는 책. KNIGA 테이블에 지정된 책 코드가 없는 경우 적절한 메시지 발행과 함께 예외가 생성되어야 합니다.
KNIGA_POSTAVKA 테이블에 새 행을 추가하는 것은 INSERT 문으로 수행됩니다. 트리거는 각 INSERT 문이 실행되기 전에 실행되어야 하므로 BEFORE 트리거 문자열이어야 합니다. 데이터의 무결성을 유지하려면 입력된 도서 코드가 KNIGA 테이블에도 있는지 확인해야 합니다. 이를 위해 한 줄 SELECT 문을 사용하여 KNIGA 테이블에서 정보를 선택합니다. 여기서 pseudo-record_BOOK_CODE 필드: new가 선택 조건에 사용됩니다. KNIGA 테이블에서 주어진 책 코드가 있는 행 수가 0이면 예외가 발생하고 적절한 메시지가 발행됩니다.
TR1 트리거 생성은 다음 명령문을 입력하여 수행됩니다.
트리거 TR1 생성 또는 교체
KNIGA_POSTAVKA에 삽입하기 전에
KNIGA에서 KOL로 카운트(*) 선택
WHERE BOOK_CODE = :NEW.BOOK_CODE;
KOL = 0이면 RAISE_APPLICATION_ERROR
(–20212, "KNIGA 테이블에 이 책에 대한 정보가 없습니다");
TR1 트리거의 효과는 KNIGA_POSTAVKA 테이블에 행을 삽입하여 TR2 트리거가 실행되도록 하는 다음 명령문을 실행하여 확인할 수 있습니다.
INSERT INTO KNIGA_POSTAVKA 값(21,15,'Ivanov',15,
북 코드 15가 KNIGA 테이블에 없기 때문에 예외가 발생하고 적절한 메시지가 발행됩니다.
2. PRICE 필드 값이 5000루블보다 큰 행을 KNIGA 테이블에 입력하는 것을 금지하는 트리거를 생성하고 KNIGA 테이블에 저장된 정보에 대한 정보인 책의 가격을 20% 이상 인상합니다. 위반한 경우 이 요구 사항적절한 메시지와 함께 예외가 발생해야 합니다.
INSERT 문의 결과로 KNIGA 테이블에 새로운 행이 추가되고, 책의 가격이 포함된 KNIGA 테이블의 PRICE 필드 값이 UPDATE 문의 결과로 변경될 수 있으므로, 트리거에 DML 문을 트리거하는 횟수가 지정됩니다. 트리거는 지정된 각 DML 문이 실행되기 전에 실행되어야 하므로 BEFORE 트리거 문자열입니다. 트리거가 수행하는 작업은 KNIGA 테이블을 수정하는 트리거 DML 문마다 다르기 때문에 해당 트리거 술어 INSERTING 및 UPDAITING을 사용하여 DML 문의 유형을 인식합니다. 새 행을 삽입하려면 PRICE 필드의 새 값을 확인해야 하고 PRICE 필드 값을 수정하면 새 값을 이전 값과 비교해야 하므로 :new 및 :old 의사 레코드를 사용해야 합니다.
TR2 트리거 생성은 다음 명령문을 입력하여 수행됩니다.
트리거 TR2 생성 또는 교체
KNIGA 가격에 대한 삽입 또는 업데이트 전
다음을 삽입하는 경우
IF:NEW.PRICE > 5000 다음
RAISE_APPLICATION_ERROR
(–20102, "KNIGA 테이블에서 책 가격 > 5000으로 레코드를 만들 수 없습니다");
업데이트하는 경우
IF:NEW.PRICE > :OLD.PRICE*1.2 THEN
RAISE_APPLICATION_ERROR
(–20103, "KNIGA 테이블에서 책의 가격을 20% 이상 변경할 수 없습니다.");
트리거 TR2의 동작은 KNIGA 테이블에 행을 삽입하고 KNIGA 테이블의 행을 업데이트하여 활성화를 일으키는 다음 명령문을 실행하여 확인할 수 있습니다.
TR2 트리거의 활성화를 일으키는 KNIGA 테이블에 행을 삽입하기 위한 명령문:
INSERT INTO KNIGA VALUES(21, "Dune", "Herbert", 5268, "Ast",
"소설");
KNIGA 테이블의 행을 업데이트하여 TR2 트리거를 활성화하는 연산자:
업데이트 KNIGA 세트 가격=6000;
이러한 진술은 책 가격의 가치 및 수정에 대한 요구 사항을 위반하므로 모든 경우에 예외가 발생하고 적절한 메시지가 발행됩니다.
3. 열을 포함하는 생성된 STAT 테이블에 트리거를 생성합니다.
출판사 이름 - IZD,
장르 "소설"의 책 수 - KOL_ROM,
"판타지" 장르의 책 수 - KOL_FAN,
표를 수정할 때마다 KNIGA는 "소설" 및 "과학 소설"이라는 지정된 주제의 맥락에서 각 출판사의 총 도서 수를 생성하고 STAT 표의 해당 열에 입력합니다.
KNIGA 테이블은 INSERT, DELETE 또는 KNIGA 테이블의 GENRE 열 값을 수정하는 UPDATE 문과 같은 DML 문을 실행하여 수정됩니다. STAT 테이블의 정보를 구성하는 작업은 KNIGA 테이블을 수정하는 각 명령문 실행 후에 수행되므로 유형별로 AFTER 트리거입니다. 트리거가 수행하는 작업은 트리거를 활성화하는 모든 유형의 명령문에 대해 동일하므로 트리거 술어가 사용되지 않습니다. 트리거를 생성하기 전에 STAT 테이블을 생성해야 합니다.
STAT 테이블 생성은 다음 명령문 세트를 입력하여 수행할 수 있습니다.
드롭 테이블 통계;
테이블 상태 생성
(IZD VARCHAR2(15),
KOL_ROM NUMBER(7),
KOL_팬 번호(7)
TR3 트리거 생성은 다음 명령문을 입력하여 수행됩니다.
트리거 TR3 생성 또는 교체
장르의 삽입, 삭제 또는 업데이트 후
커서 V1은 출판사 선택,
COUNT(NAME) KOL1
FROM KNIGA WHERE 장르 = "로맨스"
출판사별 그룹;
커서 V2는 출판사 선택,
COUNT(NAME) KOL2
KNIGA WHERE 장르 = "판타지"
출판사별 그룹;
통계에서 삭제;
V1 루프의 Z1용
통계 값에 삽입(Z1.PUBLISHER,
V2 루프의 Z1용
업데이트 통계 설정 KOL_FAN = Z1.KOL2
어디에 IZD = Z1.출판사;
SQL%를 찾을 수 없는 경우
통계 값에 삽입(Z1.PUBLISHER, 0,
KNIGA 테이블에 행을 삽입하고, 행을 삭제하고, KNIGA 테이블에서 행을 업데이트하여 TR3 트리거가 실행되도록 하는 다음 명령문을 실행하여 트리거의 동작을 확인할 수 있습니다.
TR3 트리거를 활성화하는 KNIGA 테이블의 행 삽입 문:
INSERT INTO KNIGA VALUES(46, "The Heretics of Dune", "Herbert",368,
"아스트", "판타지");
INSERT INTO KNIGA VALUES(42, "Ingvar and Alder",
"Nikitin", 168, "Ast", "Roman");
TR3 트리거의 활성화를 일으키는 KNIGA 테이블에서 행을 삭제하는 연산자:
DELETE KNIGA WHERE TITLE = "Cossacks";
TR3 트리거를 활성화하는 KNIGA 테이블의 행 수정 문:
업데이트 KNIGA SET GENRE="Sci-Fi" WHERE TITLE =
"잉바르와 알더";
다음 명령문으로 STAT 테이블의 정보를 볼 수 있습니다.