Solved

Generated Instead of trigger gives errormessage because of missing definition of tables variables for inserted and deleted

  • 22 September 2021
  • 4 replies
  • 89 views

Userlevel 2
Badge +6

I have defined a control procedure with code group INSTEAD_OF_TRIGGERS.

When i want to define the procedure on the database i get the error

Must declare the table variable "@notitie_i".

That part is missing. In an other application i see a program object item “create_table_vars”, but in this application that is missing.

Do i have to do something to define the program object item create_table_vars?

icon

Best answer by Erwin Ekkel 23 September 2021, 09:56

View original

4 replies

Userlevel 6
Badge +16

I'm not quite sure what the exact problem is you are describing. Can you post some screenshots to clarify the problem? If the screenshots holds classified information then please create a ticket for this so we can investigate the issue. 

Userlevel 2
Badge +6

The control procedure

The result is (where I'm missing the create_table_vars ):

 

And when I deploy the created procedure in SSMS I get the error message:

The following error has occured while executing the program object 'notitie_iu': Must declare the table variable "@notitie_i".
Must declare the table variable "@notitie_i".

Userlevel 6
Badge +16

I think the create table variables might be a piece of custom code for the other project you mentioned. In a trigger you can reference the inserted and deleted table. In this case you can use :

select 1
from inserted as i
where isnull(i.notitie_id,0) = 0 

Userlevel 2
Badge +6

I think the “ create table variables” is the way as it used to be in the earlier days. The other application is much older. And i tried to copy that.

With inserted and deleted it is much easier.

Thanks for helping.

Reply