CDC is two level 1- Önce, üzerinde CDC olan database var mı onu kontrol edelim USE master GO SELECT [name], database_id, is_cdc_enabled FROM sys.databases GO 2- CDC 2 step dir. DB level ve table level -- Step 1- Database level -- ENABLE FOR DATABASE USE TEST GO EXEC sys.sp_cdc_enable_db -- System Tables altında bazı tablolar oluşturulur cdc.captured_columns -- This table returns result for list of captured column. cdc.change_tables -- This table returns list of all the tables which are enabled for capture. cdc.ddl_history -- This table contains history of all the DDL changes since capture data enabled. cdc.index_columns -- This table contains indexes associated with change table. cdc.lsn_time_mapping -- This table maps LSN number (for which we will learn later) and time. -- DISABLE FOR DATABASE EXEC sys.sp_cdc_disable_db -- Step 2- Table level -- Önce track edilen tablo var mı kontrol ederiz. USE AdventureWorks GO SELECT [name], is_tracked_by_cdc FROM sys.tables GO -- ENABLE FOR TABLE EXEC sys.sp_cdc_enable_table @source_schema = N'dbo' , @source_name = N'A' , @role_name = NULL -- , @capture_instance = N'dbo_A' --, @capture_instance = NULL --, @supports_net_changes = 1 --, @captured_column_list = N'AddressID, AddressLine1, City' -- Boş bırakılırsa hepsini alır --, @filegroup_name = N'PRIMARY'; GO -- DISABLE FOR TABLE EXEC sys.sp_cdc_disable_table @source_schema=N'dbo',-- schema @source_name=N'B',-- table name @capture_instance=N'dbo_B' USE TEST GO DROP TABLE Person GO DROP TABLE Adress GO DROP TABLE Phone GO DROP TABLE Category GO CREATE TABLE Person ( PersonID INT PRIMARY KEY IDENTITY, Name varchar(20), Surname varchar(20), PhoneID INT, AdressID INT, CategoryID INT, CreateDate datetime CONSTRAINT df_PersonCreateDate DEFAULT (getdate()) ) GO INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Cuneyd','Tanrıverdi',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Mustafa','Tanrıverdi',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Selim','Çalışkan',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Özlem','Çalışkan',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Serkan','Kılıç',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Emine','Sekizkardeş',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Kıvanç','Sekizkardeş',NULL,NULL,NULL) INSERT INTO Person (Name,Surname,PhoneID,AdressID,CategoryID) VALUES ('Ümit','Ateş',NULL,NULL,NULL) GO CREATE TABLE Adress ( AddresID INT PRIMARY KEY IDENTITY, City varchar(20), Village varchar(20), AdresDescription varchar(255), CreateDate datetime CONSTRAINT df_AdresCreateDate DEFAULT (getdate()) ) GO INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Istanbul','Bakırköy','asdhakjdhk') INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Gaziantep','Değirmiçem','nmnömsfw') INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Ankara','Çayyolu','vxömvnöm adasd') INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Ankara','Çankaya','cvnmxvwo') INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Trabzon','Maçka','qwedasd') INSERT INTO Adress (City,Village,AdresDescription) VALUES ('Istanbul','Esenyurt','öşlkjo') GO /* -- USer defined data type 3 step : 1- USER DATA TYPE oluştur, 2- eğer default olacak ise DEFAULT oluştur, 3- RULE OLUŞTUR 4- Sonra DEFAULT BIND BIND et 5- Sonra RULE BIND et */ -- 1- TYPE DROP TYPE [dbo].[Phone] GO CREATE TYPE [dbo].[Phone] FROM [nvarchar](17) NULL GO -- 2- DEFAULT DROP DEFAULT [dbo].[Default_Phone] GO CREATE DEFAULT Default_Phone AS 'UnknownNumber' GO -- 3- RULE DROP RULE [dbo].[rule_Phone] GO CREATE RULE rule_Phone AS (@phone='UnknownNumber') OR (LEN(@phone)=17 AND SUBSTRING(@phone,1,1)='+' AND SUBSTRING(@phone,4,1)='-' AND SUBSTRING(@phone,8,1)='-' AND SUBSTRING(@phone,12,1)='-' AND SUBSTRING(@phone,15,1)='-') GO -- 4- BIND DEFAULT EXEC sp_bindefault 'Default_Phone', 'Phone' GO -- 5- BIND RULE EXEC sp_bindrule 'rule_Phone','Phone' CREATE TABLE Phone ( PhoneID INT PRIMARY KEY IDENTITY, CellPhone Phone, HomePhone Phone, WorkPhone Phone ) GO INSERT INTO Phone VALUES('+90-533-646-12-04',NULL,NULL) INSERT INTO Phone VALUES('+90-543-854-15-60',NULL,NULL) INSERT INTO Phone VALUES('+90-212-661-12-04',NULL,NULL) INSERT INTO Phone VALUES('+90-532-247-40-80',NULL,NULL) INSERT INTO Phone VALUES('+90-551-455-21-21',NULL,NULL) GO CREATE TABLE Category ( CategoryID INT PRIMARY KEY IDENTITY, CategoryName varchar(20) ) GO INSERT INTO Category VALUES('Belediye') INSERT INTO Category VALUES('Polis') INSERT INTO Category VALUES('İtfaiye') INSERT INTO Category VALUES('Ambulans')
