1

I try use logical tables in procedure but I have error:

"Invalid object name DELETED"

So first question can I use logical tables in procedure? If yes how can I do this?

This is a code 

USE Operator
GO
CREATE PROCEDURE generuj ( @nazwaTabeli VARCHAR(20) )
AS
BEGIN
SET NOCOUNT ON;

--
-- Check if this is an INSERT, UPDATE or DELETE Action.
-- 
DECLARE @action as char(1);

SET @action = 'I'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED) --Invalid object name DELETED
BEGIN
    SET @action = 
        CASE
            WHEN EXISTS(SELECT * FROM INSERTED) -- Invalid object name INSERTED
THEN 'U' -- Set Action to Updated.
            ELSE 'D' -- Set Action to Deleted.       
        END


END



        INSERT INTO TBLLOGI (UZYTKOWNIK,NAZWATABELI,DATAOPERACJI,TYPOPERACJI)
        VALUES (SYSTEM_USER,@nazwaTabeli,SYSDATETIME(),@action)


END

I have it from this topic Click I want use this procedure on trigger which looks like this 

CREATE TRIGGER trgLogi 
ON TBLABONENCI
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
EXEC generuj  TBLABONENCI
END;

Anybody can help me?

Community
  • 1
  • 1
user3565717
  • 13
  • 5

1 Answers1

0

inserted and deleted virtual tables are available only in triggers or as part of OUTPUT clause.If your trigger is calling a procedure, you should save them to tables first to use them from inside the procedure.

dean
  • 9,960
  • 2
  • 25
  • 26
  • *‘…available only in triggers **and** as part of OUTPUT clause’* – I think the *and* there should be an *or*. Otherwise it reads as if both conditions should be satisfied, which is not correct. – Andriy M Apr 23 '14 at 19:03