--2 IF OBJECT_ID insert_IndexView TR IS NOT NULL DROP TRIGGER insert_I

--2.1
IF OBJECT_ID ('insert_IndexView','TR') IS NOT NULL
   DROP TRIGGER insert_IndexView;
GO

CREATE TRIGGER insert_IndexView ON IndexView INSTEAD OF INSERT 
	AS 
		BEGIN
			INSERT Operation(idoper, dt) SELECT idoper, dt FROM inserted where idoper NOT IN (select idoper from Operation);
			INSERT Credit(ido, sumofmoney) SELECT idoper, sumofmoney FROM inserted;
		END;
GO
--2.2
IF OBJECT_ID ('delete_IndexView','TR') IS NOT NULL
   DROP TRIGGER delete_IndexView;
GO

CREATE TRIGGER delete_IndexView ON IndexView INSTEAD OF DELETE
	AS DELETE FROM Operation WHERE idoper IN (SELECT idoper FROM deleted);
	   DELETE FROM Credit WHERE ido IN (SELECT idoper FROM deleted);
GO
--2.3
IF OBJECT_ID ('update_IndexView','TR') IS NOT NULL
   DROP TRIGGER update_IndexView;
GO

CREATE TRIGGER update_IndexView ON IndexView INSTEAD OF UPDATE
	AS 
		IF (UPDATE(idoper)) 
			BEGIN
				/*IF EXISTS (SELECT * FROM Operation JOIN inserted ON inserted.idoper = Operation.idoper)
				BEGIN
					RAISERROR('PK exists', 16, 1);
				END
				ELSE*/
				BEGIN
					CREATE TABLE curtab1 (id1 int IDENTITY PRIMARY KEY, idnew int); 
					CREATE TABLE curtab2 (id2 int IDENTITY PRIMARY KEY, idold int);
				
					INSERT curtab1 (idnew) SELECT idoper FROM inserted;
					INSERT curtab2 (idold) SELECT idoper FROM deleted;

					UPDATE Operation SET idoper = idnew 
					FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
					WHERE Operation.idoper = curtab2.idold;
				
					UPDATE Credit SET ido = idnew 
					FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
					WHERE Credit.ido = curtab2.idold;
					DROP TABLE curtab1;
					DROP TABLE curtab2;
				END;
			END;
GO
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130907', $2000);
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130908', $4000);
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO

DELETE FROM IndexView WHERE idoper = 144;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
UPDATE IndexView SET idoper = 10 WHERE idoper = 2;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO

IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO

CREATE VIEW IndexView WITH SCHEMABINDING AS
	SELECT a.idoper, a.dt, b.sumofmoney FROM
	dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido; 
GO

SELECT * FROM IndexView;
GO