Tässä artikkelissa keskitytään erilaisiin tapoihin poistaa SQL Serverin triggerit käytöstä niin, että ne eivät häiritse tiettyjä toimintoja, kuten joukkotulosteiden lisäämistä.
Ongelma
Meillä oli tilanne, jossa meidän oli ladattava tietoja erään tietokantamme taulukoihin. Näihin taulukoihin oli luotu muutamia liipaisimia lisäystä, päivitystä ja poistoa varten, ja näille liipaisimille oli asetettu ”NOT FOR REPLICATION”. Kun tietoja ladataan taulukoihin, emme halua, että nämä liipaisimet laukeavat. Ajattelimme poistaa taulukoiden liipaisimet käytöstä, mutta tämä poistaa ne käytöstä myös muiden käyttäjien tapahtumien osalta. Haluamme, että niitä ei laukaista vain minun istuntoni osalta, josta suoritan tietojen latausskriptiä. Tässä artikkelissa katsotaan, miten SQL Serverin laukaisimet poistetaan käytöstä tietyllä tavalla.
Ratkaisu
Meidän on ensin ymmärrettävä SQL Serverin ”NOT FOR REPLICATION” -käsite.
NOT FOR REPLICATION -käsitettä voidaan käyttää, kun SQL Serverissä luodaan laukaisimia. Tämä tarkoittaa, että näitä liipaisimia ei laukaista, kun replikointiagentti tekee tietomuutoksia (INSERT / UPDATE / DELETE) tauluun.
SQL Serverin liipaisimien lisäksi tätä vihjettä voidaan käyttää myös luotaessa vierasavaimia, identiteettisarakkeita ja tarkistusrajoituksia.
Vierasavainten tapauksessa vieraan avaimen tarkistus tapahtuu vain silloin, kun käyttäjä muuttaa taulukon tietoja, eikä vieraan avaimen validointia tapahdu, kun replikointiagentti synkronoi nämä muutokset toiseen päähän (joko tilaajalle tai sekä tilaajalle että julkaisijalle konfiguroidun replikointityypin mukaan)
Identiteettisarakkeiden tapauksessa uutta identiteetti-arvoa ei luodata silloin, kun replikointiagentti lisää tietoja taulukkoon, vaan alkuperäistä identiteetti-arvoa, joka luodaan lähteenä, käytetään.
Koska SQL Serverin liipaisimet luotiin näille taulukoille ”NOT FOR REPLICATION” -merkinnällä, jos lisäämme tietoja taulukkoon tavallisena käyttäjänä, nämä liipaisimet laukeavat. Nämä liipaisimet eivät syttyneet, kun replikointiagentti lisäsi, poisti ja päivitti tietoja taulukoihin. Joten teeskentelemällä replikointiagenttia teen työni. (Toisin sanoen liipaisimet laukaistiin kaikkien käyttäjäistuntojen osalta, eikä niitä laukaistu tietyssä istunnossa, johon kirjauduin sisään replikointiagenttina.)
Tämän havainnollistamiseksi luon kaksi esimerkkitaulukkoa ”Emp” ja ”EmpJoining” sekä ”Emp”-taulukkoon liipaisimen, joka laukaistaan lisäämään liittymispäivämäärä, kun ”Emp”-taulukkoon lisätään uusi rivi.
Nyt minulla on esimerkiksi työntekijän id, nimi ja heidän liittymispäivämääränsä toisessa järjestelmässä, jotka on tuotava edellä oleviin taulukoihin. Tässä tapauksessa en halua, että laukaisulogiikka laukeaa, kun lisään tietoja Emp-taulukkoon.
Periaatteessa minulla on T-SQL-lisäysskripti molemmille näille taulukoille, joka lisää työntekijän id-nimen ”Emp”-taulukkoon ja heidän liittymispäivämääränsä ”EmpJoining”-taulukkoon.
Kirjaudutaan sisään tavallisena käyttäjänä ja suoritetaan T-SQL-skripti tietojen lisäämiseksi taulukkoon.
Kirjaudutaan sisään tavallisena käyttäjänä:
Avatkaa SQL Serverin hallintastudio. Kirjaudu nyt SQL Serveriin normaalina käyttäjänä ja käytä tietokantaa, johon olet luonut taulukon. Käytä alla olevaa skriptiä tietojen lisäämiseksi Emp-taulukkoon ja tarkista, onko liipaisin laukaistu vai ei.
|
1
2
3
3
4
4
4
4
5
5 |
INSERT INTO Emp ( Empid , name ) values ( 1 , ’Adam’ )
select * from Emp
select * from EmpJoining
|
näemme, että laukaisin on lauennut ja lisännyt tiedot EmpJoining-tauluun.
Voimme tarkistaa istunnon ominaisuuden alla olevan skriptin avulla.
|
1
|
select SESSIONPROPERTY ( ’replication_agent’ )
|
se palauttaa nollan, jos kirjaudumme sisään tavallisena käyttäjänä, ja sen palautusprosentti on yksi, jos kirjaudumme sisään replikointiagenttina.
Vaiheet kirjautumiseen replikointiagenttina:
Nyt kirjaudutaan replikointiagenttina ja lisätään tietoja skriptin avulla.
Sulje SQL Server Management Studio ja avaa se uudelleen.
Syötä palvelin, käyttäjätunnus ja salasana, jota haluat käyttää. napsauta vaihtoehtoja. Katso alla olevaa kuvaa.
Siirry lisäyhteysparametrit-välilehdelle. Kirjoita lisäyhteysparametrit-taulukkoon REPLICATION=TRUE tekstiruutuun alla olevan kuvan mukaisesti.
Voit käyttää haluamaasi sisäänkirjautumismenetelmää joko Windows-todennusta tai SQL Server -todennusta.
Näin kirjaudut sisään replikointiagenttina, ja kaikki DML-operaatiot, jotka suoritat taululle, suoritetaan replikointiagenttina.
Suorita nyt alla oleva kysely tarkistaaksesi, oletko kirjautunut sisään replikointiagenttina vai et.
|
1
|
select SESSIONPROPERTY ( ’replication_agent’ )
|
Tuloksena pitäisi olla 1.
Lisää nyt muutama rivi alla olevalla skriptillä.
|
1
2
3
3
4
|
INSERT INTO Emp ( Empid , nimi ) values ( 2 , ’Greg’ )
select * from Emp
select * from EmpJoining
|
Näemme, että liipaisinta ei ole laukaistu, koska kirjauduimme sisään replikointiagenttina ja liipaisimen arvoksi on määritetty ”EI REPLIKAATIOON”.
Tiedot lisätään vain Emp-tauluun. Katso alla olevaa kuvaa.
Tässä tapauksessa liipaisinta ei ole poistettu käytöstä, ja se on muiden käyttäjätransaktioiden käytettävissä, jotka laukaisevat liipaisimen, kun tapahtuu INSERT. Liipaisinta ei laukaista vain transaktio, joka suoritetaan replikointiagenttina.
Käytä alla olevaa kyselyä tarkistaaksesi, onko liipaisimesi merkitty ”NOT FOR REPLCIATION” -merkinnällä vai ei.
|
1
|
SELECT name,is_not_for_replication FROM SYS.triggers
|
On olemassa muitakin tapoja, joiden avulla SQL Serverin triggerit voidaan kytkeä pois päältä istunnon ajaksi käsittelemällä koodia ehdoilla.
Voit esimerkiksi käyttää CONTEXT_INFO() -toimintoa laukaisukoodissa ja palauttaa. CONTEXT_INFO():n käyttäminen ei vaadi erityisoikeuksia.
Tässä tapauksessa Jos CONTEXT_INFO vastaa liipaisimessa määritettyä arvoa, liipaisin palaa eikä suorita alla olevaa koodia. Katso alla olevaa koodia.
Tässä tapauksessa meidän on asetettava context_info arvoksi 0x1256698456 ennen tietojen lisäämistä tauluun ”Emp”.
Seuraavat järjestelmänäkymät tallentavat myös kontekstitietoja, mutta näiden näkymien suora kysely edellyttää SELECT- ja VIEW SERVER STATE -oikeuksia.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
T-SQL INSERT -skriptin sijaan oletetaan, että tiedot ovat .txt-tiedosto tai .csv-tiedosto. Voimme käyttää BCP UTILITY- tai BULK INSERT -vaihtoehtoja tietojen lataamiseksi taulukoihin laukaisematta laukaisimia.
BCP-apuohjelman käyttäminen
Voimme käyttää BCP-apuohjelmaa massatietojen lataamiseksi taulukoihin laukaisematta laukaisimia SQL Serverissä. Tämä menetelmä toimii vain INSERTEILLE taulukkoon eikä laukaise ”for insert” ja ”instead of insert” luotuja triggereitä.
Oletusarvoisesti BCP-apuohjelma ei laukaise triggereitä ladattaessa tietoja taulukoihin. Pakottaaksemme triggereiden suorittamisen, meidän pitäisi käyttää BCP:ssä -h ”FIRE_TRIGGERS”, kun lataamme tietoja taulukkoon.
Alla on esimerkkitaulukoita, joita käytetään tässä esimerkissä.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
LUO TAULUKKO KÄYTTÄJÄT
(ID int,
NAME varchar(50))
CREATE TABLE USERHIST
(USERID int,
CREATEDDATE datetime )
CREATE TRIGGER INS_USERS ON USERS
FOR INSERT
AS
BEGIN
INSERT INTO USERHIST
SELECT ID,GETDATE() FROM INSERTED
END
|
Katso alla oleva esimerkki oletusarvoisesta BCP:stä tietojen lataamiseksi tauluun ”KÄYTTÄJÄT”, joka ei laukaise SQL Serverin triggereitä. Olen peittänyt alkuperäiset palvelimen, tietokannan nimet ja kirjautumistiedot.
bcp .dbo.USERS in D:\bcp.txt -T -c -S ”SERVERNAME” -Uusername -Ppassword
Katso alla olevaa esimerkkiä BCP:stä, jossa on vihje ”FIRE_TRIGGERS” tietojen lataamiseksi USERS-taulukkoon, joka laukaisee laukaisimet.
bcp .dbo.USERS in D:\bcp.txt -T -T -c -S ”SERVERNAME” -Uusername -Ppassword -h ”FIRE_TRIGGERS”
Käyttämällä BULK INSERT
Tämäkin vaihtoehto toimii vain INSERTEILLE taulussa eikä laukaise ”for insert” ja ”instead of insert” -luodut triggereitä.
Oletusarvoisesti BULK INSERT ei laukaise triggereitä SQL Serverissä. Voimme pakottaa liipaisimen suorittamisen määrittelemällä ”FIRE_TRIGGERS”
Minulla on test.txt-tiedosto, jossa on tietoja, joiden FIELDTERMINATOR on ”,”.
Katso alla olevaa koodia oletusarvoiselle BULK INSERTille tietojen lataamiseksi USERS-taulukkoon, joka ei laukaise liipaisimia SQL Serverissä.
|
1
2
3
4
5
6
7
8
9
|
BULK
INSERT USERS
FROM ’D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ’,’,
ROWTERMINATOR = ’\n’
)
GO
|
Katso alla olevaa koodia, jossa on vihje ”FIRE_TRIGGERS”, joka laukaisee SQL Serverin triggerit ladattaessa tietoja testistä.txt-tiedosto.
|
1
2
3
4
5
6
7
8
9
10
|
BULK
INSERT USERS
FROM ’D:\\test.txt’ –location with filename
WITH
(
FIELDTERMINATOR = ’,’,
ROWTERMINATOR = ’\n’,
FIRE_TRIGGERS
)
GO
|
Tässä artikkelissa käsiteltiin SQL Serverin triggereiden käyttäytymistä alla olevissa tapauksissa.
- Kirjaudu sisään tavallisena käyttäjänä.
- Sisäänkirjautuminen replikointiagenttina ja liipaisimen asetukseksi on asetettu ei replikointia.
- CONTEXT_INFO-funktion käyttäminen liipaisinkoodissa.
- Käyttämällä BCP oletusarvoa ja vihjeellä ”FIRE_TRIGGER”
- Käyttämällä BULK INSERT oletusarvoa ja vihjeellä ”FIRE_TRIGGER”
Kohdissa 3, 4 ja 5 ei ole väliä, luodaanko liipaisimet ”EI REPLIKAATIOON” vai ei.
- Author
- Recent Posts
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases – October 24, 2019
- Collate SQL -komennon yleiskatsaus – 22. lokakuuta 2019
- Kadonneen SA-salasanan palauttaminen – 20. syyskuuta 2019