Can some body give me some example of INSTEAD OF trigger?

Question

Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed

Answer 1

CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1]  VALUES('a1','John')
GO

--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead OF INSERT AS
BEGIN
DECLARE @ch CHAR
DECLARE @num INT
SELECT  @num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT  @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET @num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO [dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]

Answer 2

Enter Answer here INSTEAD-OF triggers provide a transparent way of modifying views that cannot be modified directly through SQL DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD-OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

CREATE TABLE dept (deptno NUMBER PRIMARY KEY,
deptname VARCHAR2(20),
manager_num NUMBER
);

CREATE TABLE emp (empno NUMBER PRIMARY KEY,
empname VARCHAR2(20),
deptno NUMBER REFERENCES dept(deptno),
startdate DATE
);

CREATE VIEW manager_info AS
SELECT d.deptno, d.deptname, e.empno, e.empname
FROM emp e, dept d
WHERE e.empno = d.manager_num;

CREATE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information
FOR EACH ROW
DECLARE
empCount NUMBER;
BEGIN

/* First check to make sure that the number of employees
in the department is greater than one */
SELECT COUNT(*) INTO empCount
FROM emp e
WHERE e.deptno = :n.deptno;

/* If there are enough employees then make him or her the manager */
IF empCount >= 1 THEN

UPDATE dept d
SET manager_num = :n.empno
WHERE d.deptno = :n.deptno;

END IF;
END;
/