Triggers in SQL

I have created an AFTERINSERT trigger & INSTEADOFINSERT trigger on tables.

Code:

---

create trigger trig_AFTERINSERT
on original
FOR INSERT AS
BEGIN
INSERT trigger_original(Roll,UserName,Password)
SELECT ROLL,Name,Name+'124'
FROM INSERTED
END

---

Code:

---

insert into original values(1,'A')

---

WHen I execute the above statemnet, 2 Rows get inserted. ONE ROW IS INSERTED in TABLE - original, ANOTHER ROW GET INSERTED IN TABLE - trigger_original.

NOW I HAVE CREATED an INSTEADOFINSERT Trigger on the same tables.

Code:

---

create trigger trig_INSTEADOFINSERT
on original
INSTEAD OF INSERT
AS
INSERT trigger_original(Roll,UserName,Password)
SELECT ROLL,Name,Name+'124'
FROM INSERTED

---

Code:

---

insert into original values(2,'B')

---

WHen the above insert statement is executed, I get message 2 Rows Affected.
But when i select the records

Code:

---

Select * from original

---

- IT CONTAINS ONLY THE ONE ROW ( WITH ROLL NO 1)

Code:

---

Select * from trigger_original

---

( TWO ROWS ARE THERE - CORRECT)

WHy the row is not inserted into table original that is inserted using the INSTEADOF_INSERT Trigger.

because you told it to insert into the trigger_original table... not original...

Code:

---

INSERT trigger_original(Roll,UserName,Password)
SELECT ROLL,Name,Name+'124'
FROM INSERTED

---

It did exactly what you told it to do...

That said... it is DANGEROUS to insert into the same table you're triggering off of... you could end up in an endless loop that could cause the system to crash...

-tg