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