create procedure sp_upd_tid4asset(@dis_hw_uuid binary(16), @tenant_id binary(16))
as
begin
/*
this procedure have to be called every time if a tenant_id was changed for an asset
i.e. called by trigger on  ca_discovered_hardware after update
*/

    declare @tntree varchar(64);
    declare @tnitem varchar(64);
    declare @tblowner nvarchar(255);

    DECLARE mycur CURSOR local
        FOR select table_name_tree, table_name_item, tbl_owner from inv_table_map;
        
    
    declare @updSql varchar(255);    
    

    OPEN mycur
    FETCH NEXT FROM mycur INTO @tntree, @tnitem, @tblowner;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        
        if( @tblowner is not null)
        begin -- prefix table name
            set @tntree = @tblowner + '.' + @tntree;
            set @tnitem = @tblowner + '.' + @tnitem;
        end;
        
        set @updSql = 'update ' +  @tntree + ' set tenant_id=0x'+dbo.hex(@tenant_id)+' where object_uuid=0x'+dbo.hex(@dis_hw_uuid);
        EXECUTE  (@updSql);

        set @updSql = 'update ' +  @tnitem + ' set tenant_id=0x'+dbo.hex(@tenant_id)+' where object_uuid=0x'+dbo.hex(@dis_hw_uuid);
        EXECUTE  (@updSql);
        
        FETCH NEXT FROM mycur INTO @tntree, @tnitem, @tblowner;

    END

    CLOSE mycur
    DEALLOCATE mycur

    set @updSql = 'update  ca_agent set tenant_id=0x'+dbo.hex(@tenant_id)+' where object_uuid=0x'+dbo.hex(@dis_hw_uuid);
    EXECUTE  (@updSql);


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