Apr 4, 2013

The target table '' of the INSERT statement cannot have any enabled triggers when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.


Suppose we have created Employee table in current database. Now we are creating other table tblData:

CREATE TABLE tbldata(
     Data VARCHAR(100),
     DeletedDate DATE DEFAULT(GETDATE())
)

Creating a trigger on table tblData:

CREATE TRIGGER trgData ON tbldata
AFTER INSERT
AS
     IF EXISTS(SELECT 1 FROM INSERTED WHERE Data IS NULL)
          RAISERROR('Cannot insert Null',16,100)

If we will execute following sql query:

INSERT INTO tbldata(Data)
SELECT  Name
FROM(
     DELETE TOP(1)
     FROM Employee
     OUTPUT DELETED.Name
) Temp

We will get error message :

The target table '' of the INSERT statement cannot have any enabled triggers when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Cause: We cannot insert records into a table which has any defined trigger from nested from clause.  

Solution:

So we must have to drop the trigger on table tbldata

No comments:

Post a Comment