How to prevent a self-referencing table from becoming circular

You can include a check in your UPDATE statement:

DECLARE @Employee INT = 2
       ,@NewManager INT = 4
;WITH cte AS (SELECT *
              FROM tblEmployee
              WHERE Manager = @Employee
              UNION  ALL
              SELECT a.*
              FROM tblEmployee a
              JOIN cte b
                ON a.manager = b.EmployeeID)
UPDATE a
SET a.Manager = @NewManager
FROM tblEmployee a
WHERE EmployeeID = @Employee
    AND NOT EXISTS (SELECT *
                    FROM cte b
                    WHERE a.EmployeeID = b.Manager)

Demo: SQL Fiddle

You can do this with a CHECK CONSTRAINT that validates manager id is not a cycle. You can't have complex queries in a check constraint, but if you wrap it in a function first you can:

create function CheckManagerCycle( @managerID int )
returns int
as
begin

    declare @cycleExists bit
    set @cycleExists = 0

    ;with cte as (
        select E.* from tblEmployee E where ID = @managerID
        union all
        select E.* from tblEmployee E join cte on cte.ManagerID = E.ID and E.ID <> @managerID
    )
    select @cycleExists = count(*) from cte E where E.ManagerID = @managerID

    return @cycleExists;

end

Then you can use a constraint like this:

alter table tblEmployee
ADD CONSTRAINT chkManagerRecursive CHECK ( dbo.CheckManagerCycle(ManagerID) = 0 )

This will prevent adding or updating records to create a cycle from any source.

---

Edit: An important note: check constraints are validated on the columns they reference. I originally coded this to check cycles on the Employee ID, rather than the Manager ID. However, that did not work because it only triggered on changes to the ID column. This version does work because it is triggered any time the ManagerID changes.

You can add 'level' integer column.

Alice and Dave will have level == 0 If You will set manager for employee his (employee) level will be level+1 of his manager.

During update You should check if manager level is smaller than level of employee...

This will be faster than using procedure...