데이터베이스 트리거

데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다. 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다.

유형

트리거에는 크게 나누어 행 트리거와 문장 트리거의 두 종류가 있다.

  • 행 트리거: 테이블 안의 영향을 받은 행 각각에 대해 실행된다. 변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다.
  • 문장 트리거:INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다.

또한 트리거는 다음과 같은 속성을 갖는다.

  • BEFORE 또는 AFTER
트리거가 실행되는 시기를 지정한다.
  • INSTEAD OF
트리거를 원래 문장 대신 수행한다.
  • WHEN
트리거를 시작하는 조건식을 지정한다.

일반적으로 트리거는 다음의 3 가지 경우에 시작된다. 트리거는 SELECT 문에 의한 데이터 검색에 영향을 미칠 수 없다.

  • INSERT (새로운 행 삽입)
  • UPDATE (기존 행의 변경) / UPDATE OF (기존 행의 특정 열 변경)
  • DELETE (기존 행 삭제)

오라클 트리거

오라클 데이터베이스는 표준 트리거 외에 Oracle 9i에서 "스키마 레벨 트리거"라는 스키마 변경 및 사용자 로그인 / 로그 오프에 대한 트리거가 추가되었다.

  • INSERT
  • UPDATE
  • DELETE
  • AFTER CREATE
  • BEFORE / AFTER ALTER
  • BEFORE / AFTER DROP
  • BEFORE LOGOFF / AFTER LOGON

마이크로소프트 SQL 서버 트리거

Microsoft SQL Server에서는 표준 트리거 외에 뷰 에 INSTEAD OF 트리거를 정의 할 수 있다. 또한 Microsoft SQL Server 2005에서 데이터 정의 언어(DDL) 트리거가 추가되었다.

  • INSERT
  • UPDATE
  • DELETE
  • DROP TABLE
  • CREATE TABLE
  • ALTER TABLE
  • 로그인

PostgreSQL 트리거

PostgreSQL는 버전 6.2 (1997년)에서 표준 트리거가 지원되고 있다. 또한 버전 8.4에서 TRUNCATE 이벤트, 9.0에서 UPDATE OF, WHEN 절을 지원했다.

  • INSERT
  • UPDATE / UPDATE OF
  • DELETE
  • TRUNCATE

트리거에서 실행되는 프로세스는 직접 작성하지 않고 함수로 준다.

문법 : 

 CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
     ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
     [ WHEN (...) ] EXECUTE PROCEDURE funcname ( arguments )

MySQL 트리거

MySQL은 버전 5.0.2에서 표준 트리거가 지원되었다.

  • INSERT
  • UPDATE
  • DELETE

주의: MySQL은 각 테이블에 각 형태의 단 하나의 트리거만 허용한다. (즉, 인서트 이전에 하나, 인서트 이후에 하나, 업데이트 이전에 하나, 업데이터 이후에 하나, 삭제 전후에 각각 하나씩)

주의: MySQL은 구문을 외부에서 격발하지 않는다. (즉, API, 외래 키 캐스캐이드)

문법 : 

 CREATE TRIGGER salary_trigger
     BEFORE UPDATE ON employee_table
     REFERENCING NEW ROW AS n, OLD ROW AS o
     FOR EACH ROW
     IF n.salary <> o.salary THEN
        ...
     END IF;


외부 링크

Read other articles:

Kepler-90

Kepler-90 Perbandingan sistem Kepler-90 dengan Tata Surya (14 Desember 2017). Data pengamatan Epos 2000      Ekuinoks 2000 Rasi bintang Draco Asensio rekta  18j 57m 44d Deklinasi  +49° 18′ 19″ Magnitudo tampak (V) 14 Ciri-ciri Kelas spektrum G0V AstrometriJarak2545[1] tc (780[1] pc) DetailMassa1.2 ± 0.1[1] M☉Radius1.2 ± 0.1[1] R☉Gravitasi permukaan (log...

 

KidZania

KidZaniaLogo KidZaniaJenisPusat Hiburan KeluargaDidirikan1 September 1999; 24 tahun lalu (1999-09-01)Situs webkidzania.com KidZania adalah sebuah waralaba swasta Meksiko berupa pusat hiburan keluarga yang saat ini beroperasi di 23 lokasi, yang memungkinkan anak-anak melakukan pekerjaan orang dewasa dan mendapat bayaran. KidZania telah dikunjungi oleh lebih dari 31 juta pengunjung di seluruh dunia sejak pembukaannya, menjadikannya salah satu merek hiburan global yang paling cepat berkemba...

 

Sindrom Horner

Sindrom HornerSindrom Horner sisi tubuh sebelah kiriInformasi umumNama lainSindrom Bernard-Horner (BH), palsi okulosimpatetikSpesialisasiNeurologi  Sindrom Horner adalah sekumpulan gejala akibat kerusakan pada jalur saraf dari otak ke wajah. Sindrom ini dapat terjadi akibat penyakit lain atau malah tidak diketahui penyebabnya.[1] Sindrom Horner ditandai dengan mengecilnya salah satu pupil mata dan terkulainya salah satu kelopak mata (ptosis). Meski tidak berbahaya, kondisi ini bi...

Scott Chipperfield

Scott Chipperfield Informasi pribadiNama lengkap Scott Kenneth Chipperfield[1]Tanggal lahir 30 Desember 1975 (umur 48)Tempat lahir Sydney, AustraliaTinggi 1,80 m (5 ft 11 in)Posisi bermain Sayap kiriInformasi klubKlub saat ini FC AeschKarier junior Bellambi Rosellas Tarrawanna FernhillKarier senior*Tahun Tim Tampil (Gol)1996–2001 Wollongong City 131 (50)2001–2012 Basel 269 (69)2012– FC Aesch Tim nasional‡1998–2010 Australia 68 (12) * Penampilan dan gol d...

 

Quebec

Province of Canada This article is about the Canadian province. For the province's capital city, see Quebec City. For the historical province, see Province of Quebec (1763–1791). For other uses, see Quebec (disambiguation). This article may be too long to read and navigate comfortably. When this tag was added, its readable prose size was 15,200 words. Consider splitting content into sub-articles, condensing it, or adding subheadings. Please discuss this issue on the article's talk page. (Ju...

 

Ortholeptura

Ortholeptura Ortholeptura valida Klasifikasi ilmiah Kerajaan: Animalia Filum: Arthropoda Kelas: Insecta Ordo: Coleoptera Famili: Cerambycidae Genus: Ortholeptura Ortholeptura adalah genus kumbang tanduk panjang yang tergolong famili Cerambycidae. Genus ini juga merupakan bagian dari ordo Coleoptera, kelas Insecta, filum Arthropoda, dan kingdom Animalia. Larva kumbang dalam genus ini biasanya mengebor ke dalam kayu dan dapat menyebabkan kerusakan pada batang kayu hidup atau kayu yang telah di...

ロバート・デ・ニーロ

ロバート・デ・ニーロRobert De Niro 2011年のデ・ニーロ生年月日 (1943-08-17) 1943年8月17日（80歳）出生地 アメリカ合衆国・ニューヨーク州ニューヨーク市身長 177 cm職業 俳優、映画監督、映画プロデューサージャンル 映画、テレビドラマ活動期間 1963年 -配偶者 ダイアン・アボット（1976年 - 1988年）グレイス・ハイタワー（1997年 - ）主な作品 『ミーン・ストリート』（1973年）...

 

Torta del Casar

This article includes a list of references, related reading, or external links, but its sources remain unclear because it lacks inline citations. Please help improve this article by introducing more precise citations. (March 2013) (Learn how and when to remove this template message) Torta del CasarCountry of originSpainRegion, townCasar de Caceres, CaceresRegionExtremaduraSource of milkSheepPasteurisedNoTextureSemi-hardAging time60 days minimumCertificationprotected-origin status 2003Named af...

 

Herod Agrippa

King of Judaea (11 BC–AD 44) (r. 41–AD 44) For his son, see Herod Agrippa II. Herod Agrippa IKing of JudaeaReignAD 41–44PredecessorMarullus (Prefect of Judea)SuccessorCuspius Fadus (Procurator of Judea)Bornc. 11 BCDiedc. AD 44 (aged about 54)Caesarea MaritimaSpouseCypros, daughter of Phasael II, son of Phasael I (brother of Herod the Great)IssueAgrippa IIBereniceMariamneDrusillaNamesMarcus Julius AgrippaDynastyHerodian DynastyFatherAristobulus IVMotherBerenice Herod Agrippa ...

Ravine Daniel

Cet article est une ébauche concernant Saint-Paul de La Réunion. Vous pouvez partager vos connaissances en l’améliorant (comment ?) selon les recommandations des projets correspondants. Ravine Daniel Administration Pays France Région La Réunion Département La Réunion Commune Saint-Paul Géographie Coordonnées 21° 05′ 19,5″ sud, 55° 18′ 22,5″ est Localisation Géolocalisation sur la carte : La Réunion Ravine Daniel Géolocalisation ...

 

约翰斯顿环礁

此條目可参照英語維基百科相應條目来扩充。 (2021年5月6日)若您熟悉来源语言和主题，请协助参考外语维基百科扩充条目。请勿直接提交机械翻译，也不要翻译不可靠、低品质内容。依版权协议，译文需在编辑摘要注明来源，或于讨论页顶部标记{{Translated page}}标签。 约翰斯顿环礁Kalama Atoll 美國本土外小島嶼 Johnston Atoll 旗幟颂歌：《星條旗》The Star-Spangled Banner約翰斯頓環礁�...

 

Gabinetto Vieusseux

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed.Find sources: Gabinetto Vieusseux – news · newspapers · books · scholar · JSTOR (September 2019) (Learn how and when to remove this message) Gabinetto Vieusseux43°46′17″N 11°15′10″E / 43.77139468427455°N 11.25265081310055°E / 43.7713946842...

Danny Simpson

English footballer (born 1987) For the Brookside character, see Sean Smith (Brookside). For the footballer of the early 20th century, see Danny Simpson (early footballer). Danny Simpson Simpson in 2009Personal informationFull name Daniel Peter Simpson[1]Date of birth (1987-01-04) 4 January 1987 (age 37)[2]Place of birth Eccles, EnglandHeight 5 ft 9 in (1.76 m)[3]Position(s) Right-backTeam informationCurrent team MacclesfieldYouth career2003–2006 M...

 

Siege of Pamplona (1813)

1813 siege during the Peninsular War Siege of Pamplona (1813)Part of Peninsular WarPart of the Pamplona fortress.Date26 June – 31 October 1813LocationPamplona, Spain42°49′N 01°39′W / 42.817°N 1.650°W / 42.817; -1.650Result Allied victoryBelligerents Kingdom of Spain United Kingdom First French EmpireCommanders and leaders Henry O'Donnell Carlos de España Marquess Wellington Lord Dalhousie Louis CassanStrength 9,500–14,183, 12 guns 3,450–3,800 80 heavy &...

 

Electrophile

A chemical species that accepts an electron pair from a nucleophile In chemistry, an electrophile is a chemical species that forms bonds with nucleophiles by accepting an electron pair.[1] Because electrophiles accept electrons, they are Lewis acids.[2] Most electrophiles are positively charged, have an atom that carries a partial positive charge, or have an atom that does not have an octet of electrons. Electrophiles mainly interact with nucleophiles through addition and subs...

Andaman Kadhali

1978 film Andaman KadhaliPosterDirected byMuktha SrinivasanWritten byA. S. PrakasamProduced byMuktha RamaswamyStarringSivaji GanesanSujathaCinematographyR. SampathEdited byV. P. KrishnanMusic byM. S. ViswanathanProductioncompanyMuktha FilmsRelease date 26 January 1978 (1978-01-26) CountryIndiaLanguageTamil Andaman Kadhali (transl. Lover from Andaman) is a 1978 Indian Tamil-language film, directed by Muktha Srinivasan, produced by Muktha Ramaswamy and written by A. S. Prak...

 

Evzones

Unit type of the Hellenic Army Evzones of the Hellenic Army, c. 1900 The Evzones or Evzonoi (Greek: Εύζωνες, Εύζωνοι, pronounced [ˈevzones, ˈevzoni]) were a type of light infantry units in the Hellenic Army. Today, they are the members of the Presidential Guard (Greek: Προεδρική Φρουρά, romanized: Proedrikí Frourá), a ceremonial unit that guards the Greek Tomb of the Unknown Soldier (Greek: Μνημείο του Άγνωστου Στρατι...

 

Alexander Elliott

Untuk politikus di Ontario, Kanada, lihat Alexander Leslie Elliott. Alexander C. ElliottLahir1831Beaver County, PennsylvaniaMeninggalFebruary 9, 1905Pittsburgh, PennsylvaniaDikebumikanHighwood Cemetery, Pittsburgh, PennsylvaniaPengabdianAmerika SerikatDinas/cabangAngkatan Darat Amerika Serikat (Union Army)PangkatSersanKesatuan 1st Pennsylvania Cavalry, Company APerang/pertempuranPerang Saudara Amerika: Pertempuran Cross Keys Pertempuran Cedar Mountain Pertempuran Thoroughfare Gap Pertempuran ...

Lichtensteig

Cet article est une ébauche concernant une localité suisse. Vous pouvez partager vos connaissances en l’améliorant (comment ?) selon les recommandations des projets correspondants. Lichtensteig Rue principale et hôtel de ville. Armoiries Drapeau Administration Pays Suisse Canton Saint-Gall Circonscription électorale Toggenburg Communes limitrophes Oberhelfenschwil, Bütschwil, Wattwil NPA 9620 No OFS 3374 Démographie Population permanente 1 980 hab. (31 décembre ...

 

Grande Loëze

Pour les articles homonymes, voir Loëze (homonymie). la Grande Loëzeruisseau de la Petite Loëze La Grande Loëze entre les deux Bâgé. Caractéristiques Longueur 11,8 km [1] Bassin 27,6 km2 [2] Bassin collecteur Bassin de la Saône (d) Débit moyen (Feillens) [2] Nombre de Strahler 2 Cours Source au lieu-dit Trélie · Localisation Bâgé-Dommartin · Altitude 203 m · Coordonnées 46° 18′ 32″ N, 5° 00′ 04″ E Confluence Virolet · Lo...