--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