create procedure sp_register_newinvitmtbl(@invTblname varchar(255), @tblowner nvarchar(128))
as
begin
/**
this procedure will be called if a new inventory item table is registered
in the database table inv_table_map

@param tblname  full qualified table name e.g. ca_itrm.inv_xxx_item
*/

    declare @trgName varchar(64);
    declare @sqlStmt varchar(1024);
    declare @dropTrigStmt varchar(1024);

    set @trgName = 'r_'+  SUBSTRING(@invTblname, 3, LEN(@invTblname)-3);

    if( @tblowner is not null)
        begin -- prefix table name
            set @invTblname = @tblowner + '.' + @invTblname;
        end;

    set @dropTrigStmt = 'drop trigger ' + @trgName ;

    set @sqlStmt = 'create trigger ' + @trgName
     + ' on ' + @invTblname
     + ' after insert '
        + ' as '
        + ' begin '
    + '    update ' + @invTblname + ' set tenant_id=a.tenant_id'
    + '             from ca_agent a , inserted newinv '
    + '             where a.object_uuid = '+ @invTblname + '.object_uuid '
    + '               and ' + @invTblname + '.object_uuid = newinv.object_uuid '
    + '               and ' + @invTblname + '.item_name_id = newinv.item_name_id '
    + '               and ' + @invTblname + '.item_parent_id = newinv.item_parent_id; '
    + ' end;'

    

    -- add trigger to inv table

    if EXISTS (Select * From dbo.sysobjects Where id =
        Object_ID(@trgName) and OBJECTPROPERTY(id, N'IsTrigger')=1)
    begin
        -- trigger exists
    
        execute (@dropTrigStmt);
    end;

    -- create trigger
    EXECUTE  (@sqlStmt);
        

end;
GO
GRANT EXECUTE ON  [dbo].[sp_register_newinvitmtbl] TO [ca_itrm_group]
GO