--CREATE TABLE phonebook ( id INT PRIMARY KEY,name VARCHAR(255) NOT NULL,phone VARCHAR(20) NOT NULL,email VARCHAR(255));
-------------------------------------------------------------------------------------------------------------------------------------------
--INSERT INTO phonebook (id, name, phone, email) VALUES (1, 'Talha Eren Arslan', '555-555-5555', 'tea@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (2, 'Bugra Özer', '555-444-5555', 'bö@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (3, 'Tayfun Eröl', '555-555-5111', 'te@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (4, 'Atacan Ugurcuklu', '111-555-5555', 'atu@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (5, 'Mukadder Kırankırana', '333-555-5555', 'mkdir@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (6, 'Ahmet Turan Koçak', '555-525-5555', 'atk@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (7, 'Yusuf Özbakır', '125-555-5555', 'yöz@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (8, 'Tayyip Topgül', '555-555-5335', 'tt@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (9, 'Eren Arslan', '555-165-5555', 'ea@gmail.com');
--INSERT INTO phonebook (id, name, phone, email) VALUES (10, 'Yusuf Altuntas', '111-111-1111', 'ya@gmail.com');
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE KOMUTLAR
AS
BEGIN
          print 'bütün rehberi görüntülemek için --- exec all_contact'
	  print 'arama yapmak için --- exec search_contacts aranan isim ya da karakter '
	  print 'rehbere kayıt yapmak icin --- exec insert_contact id,name,phone,email'
	  print 'rehberdeki kayıtı update için --- exec update_contact id,name,phone,email'
	  print 'rehberden kayıt silme işlemi için --- exec delete_contact id , name'
	  print  'Silinen Kayıtları Görmek İçin --exec all_delcontact'
     
END
-------------------------------------------------------------------------------------------------------------------------------------------
--exec KOMUTLAR
--exec all_contact
--exec search_contacts aranan yusuf
--exec insert_contact id,name,phone,email
--exec update_contact id,name,phone,email
--exec delete_contact id ,name
--exec all_delcontact
-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE search_contact( @name VARCHAR(255)) // arama procedure -----------------------------------------------------
--AS
--BEGIN
--    SELECT * FROM phonebook WHERE name LIKE CONCAT('%',@name,'%');
--END;
--exec search_contact ahmet
-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE update_contact(@id INT, @name VARCHAR(255), @phone VARCHAR(20), @email VARCHAR(255)) //update procedure--------------
--AS
--BEGIN
--    UPDATE phonebook SET name = @name,phone = @phone,email = @email WHERE id = @id;
--END;
--EXEC update_contact 1, 'Talha Eren Arslan', '521-555-5555', 'ttt@gmail.com';


-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE insert_contact(@id INT, @name VARCHAR(255),@phone VARCHAR(20),@email VARCHAR(255)) //rehbere eklme---------------------
--AS
--BEGIN
--   declare @var int
--   Select @var = COUNT(1) from phonebook Where id=@id AND name=@name
--     if(@var = 0)
--	 begin
--	 INSERT INTO phonebook (id, name, phone, email) VALUES (@id,@name,@phone,@email)
	 
--	 print 'eklendi'
--	 print @name 
--	 print @phone
--	 end
--	 else print 'id farklı giriniz'
--END
--exec insert_contact 12,'ahmet kral','515-112-1234','ak@gmail.com'

-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE all_contact // butun rehberi görüntüleme-------------------------------
--AS
--BEGIN
--     Select * from phonebook
--	 END
--exec all_contact

--CREATE PROCEDURE delete_contact(@id INT, @name VARCHAR(255)) //rehberden silme 
--AS
--BEGIN
--   DELETE  FROM phonebook  WHERE id=@id AND name=@name
--   print 'silindi'
--   print @name
--END
--exec delete_contact 12,'ahmet kral'
-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE TABLE delete_kayıt ( id INT PRIMARY KEY,name VARCHAR(255) NOT NULL,phone VARCHAR(20) NOT NULL,email VARCHAR(255));
-------------------------------------------------------------------------------------------------------------------------------------------
--CREATE TABLE delete_kayıt ( id INT PRIMARY KEY,name VARCHAR(255) NOT NULL,phone VARCHAR(20) NOT NULL,email VARCHAR(255));
--CREATE TRIGGER dlt on phonebook
--Instead of delete
--as
--begin
-- declare @tmp int
-- declare @tmp1 varchar(255)
-- select @tmp1=name from deleted
-- select @tmp=id from deleted
-- declare @id int,@adi varchar(255),@phone varchar(20),@email varchar(255)
-- declare delcrs cursor for
--   select id,name,phone,email from deleted
--   open delcrs 
--   fetch next from delcrs into @id,@adi,@phone,@email
--    while @@FETCH_STATUS=0
--	begin
--	insert into delete_kayıt values(@id,@adi,@phone,@email)
--	fetch next from delcrs into @id,@adi,@phone,@email
--	end
--	close delcrs
--	deallocate delcrs
--	delete from phonebook where id=@tmp
--	print 'silindi'
--	print @tmp
--	print @tmp1
--	end
--------------------------------------------------------------------------------------------------------------------------------------------
--CREATE PROCEDURE all_delcontact // silinen kayırları görüntüleme
--AS
--BEGIN
--  Select * from delete_kayıt
-- END