CODEWITHSUNDEEP
sqlsql-server
Lalu
Lalu

Reputation: 178

create trigger after delete

When a row is deleted from titles_in_stock table, I want to insert equivalent row in table named titles_in_stock_out.

I tried the following

create trigger titles_in_stock_out
   on titles_in_stock
   after delete as
   begin
      insert into titles_in_stock_out
      (cd_title, invenotry, cd_type)
      values
      (deleted.cd_title, deleted.invenotry, deleted.cd_type)
   end

but this gives following error when tried to execute above statement.

Msg 128, Level 15, State 1, Procedure titles_in_stock_out, Line 8
The name "deleted.cd_title" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any Help?

Thanks

Upvotes: 1

Views: 164

Answers (4)

Prashant Kumar
Prashant Kumar

Reputation: 249

This should solve your problem :

IF OBJECT_ID('titles_in_stock_out', 'TR') IS NOT NULL
DROP TRIGGER titles_in_stock_out
GO
CREATE TRIGGER titles_in_stock_out
ON titles_in_stock
FOR DELETE 
AS
BEGIN
      INSERT INTO titles_in_stock_out
      (cd_title, invenotry, cd_type)
      VALUES
      (cd_title, invenotry, cd_type)
      SELECT 
      cd_title, invenotry, cd_type
      FROM titles_in_stock
END

Upvotes: -1

jpw
jpw

Reputation: 44871

You need to refer todeletedas a table, and also the trigger needs to have a different name; in your example it had the same name as the table.

create trigger titles_in_stock_out_trig
   on titles_in_stock
   after delete as
   begin
      insert into titles_in_stock_out
      (cd_title, invenotry, cd_type)
      select 
      cd_title, invenotry, cd_type
      from deleted
   end

Upvotes: 1

mohan111
mohan111

Reputation: 8865

    create trigger titles_in_stock_out
       on titles_in_stock
       FOR  delete 
       begin
   insert into titles_in_stock_out
      (cd_title, invenotry, cd_type)
      select 
      cd_title, invenotry, cd_type
      from deleted
       end

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Your syntax is incorrect. The deleted is virtual table which available in trigger, then you must refer it as table.

create trigger titles_in_stock_out
   on titles_in_stock
   after delete as
   begin
      insert into titles_in_stock_out
      (cd_title, invenotry, cd_type)
      select cd_title, invenotry, cd_type
      from deleted
   end

This will helpful in cases when you deleted more than one row.

Upvotes: 6

Related Questions