Editing HumanResources.Employee (table)

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table
| HumanResources.Employee
|- valign="top"
| '''Description
| Employee information such as salary, department, and title.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description

|- valign="top"
| EmployeeID
| int
| not null
| 
| Primary key for Employee records.
|- valign="top"
| NationalIDNumber
| nvarchar(15)
| not null
| 
| Unique national identification number such as a social security number.
|- valign="top"
| ContactID
| int
| not null
| 
| Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
|- valign="top"
| LoginID
| nvarchar(256)
| not null
| 
| Network login.
|- valign="top"
| ManagerID
| int
| null
| 
| Manager to whom the employee is assigned. Foreign Key to Employee.M
|- valign="top"
| Title
| nvarchar(50)
| not null
| 
| Work title such as Buyer or Sales Representative.
|- valign="top"
| BirthDate
| datetime
| not null
| 
| Date of birth.
|- valign="top"
| MaritalStatus
| nchar(1)
| not null
| 
| M = Married, S = Single
|- valign="top"
| Gender
| nchar(1)
| not null
| 
| M = Male, F = Female
|- valign="top"
| HireDate
| datetime
| not null
| 
| Employee hired on this date.
|- valign="top"
| SalariedFlag
| dbo.Flag
| not null
| ((1))
| Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
|- valign="top"
| VacationHours
| smallint
| not null
| ((0))
| Number of available vacation hours.
|- valign="top"
| SickLeaveHours
| smallint
| not null
| ((0))
| Number of available sick leave hours.
|- valign="top"
| CurrentFlag
| dbo.Flag
| not null
| ((1))
| 0 = Inactive, 1 = Active
|- valign="top"
| rowguid
| uniqueidentifier
| not null
| (newid())
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
|- valign="top"
| ModifiedDate
| datetime
| not null
| (getdate())
| Date and time the record was last updated.
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|- valign="top"
| PK_Employee_EmployeeID
| EmployeeID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns

|- valign="top"
| AK_Employee_LoginID
| Unique
| LoginID
|- valign="top"
| AK_Employee_NationalIDNumber
| Unique
| NationalIDNumber
|- valign="top"
| AK_Employee_rowguid
| Unique
| rowguid
|- valign="top"
| IX_Employee_ManagerID
| 
| ManagerID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description

|- valign="top"
| CK_Employee_BirthDate
| ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
| Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())
|- valign="top"
| CK_Employee_Gender
| (upper([Gender])='F' OR upper([Gender])='M')
| Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'
|- valign="top"
| CK_Employee_HireDate
| ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
| Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())
|- valign="top"
| CK_Employee_MaritalStatus
| (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
| Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'
|- valign="top"
| CK_Employee_SickLeaveHours
| ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
| Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)
|- valign="top"
| CK_Employee_VacationHours
| ([VacationHours]>=(-40) AND [VacationHours]<=(240))
| Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column

|- valign="top"
| [[Person.Contact_(table)|Person.Contact]]
| ContactID
| ContactID
|- valign="top"
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
| ManagerID
| EmployeeID
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column

|- valign="top"
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
| EmployeeID
| ManagerID
|- valign="top"
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
| EmployeeID
| EmployeeID
|- valign="top"
| [[HumanResources.EmployeeDepartmentHistory_(table)|HumanResources.EmployeeDepartmentHistory]]
| EmployeeID
| EmployeeID
|- valign="top"
| [[HumanResources.EmployeePayHistory_(table)|HumanResources.EmployeePayHistory]]
| EmployeeID
| EmployeeID
|- valign="top"
| [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]]
| EmployeeID
| EmployeeID
|- valign="top"
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| EmployeeID
| EmployeeID
|- valign="top"
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
| EmployeeID
| SalesPersonID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Trigger
| '''Type

|- valign="top"
| dEmployee
| INSTEAD OF DELETE
|}

==== Trigger dEmployee ====
<pre>
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;
</pre>

=== References ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Dependency Type
| '''Object Type
| '''Referenced Object

|- valign="top"
| Data Type
| Type
| [[dbo.Flag_(type)|dbo.Flag]]

|- valign="top"
| Schema
| Schema
| [[HumanResources_(schema)|HumanResources]]

|}

=== Dependencies ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Reference Type
| '''Object Type
| '''Referencing Object

|- valign="top"
| Select
| View
| [[HumanResources.vEmployee_(view)|HumanResources.vEmployee]]

|- valign="top"
| Select
| View
| [[HumanResources.vEmployeeDepartment_(view)|HumanResources.vEmployeeDepartment]]

|- valign="top"
| Select
| View
| [[HumanResources.vEmployeeDepartmentHistory_(view)|HumanResources.vEmployeeDepartmentHistory]]

|- valign="top"
| Select
| View
| [[Sales.vSalesPerson_(view)|Sales.vSalesPerson]]

|- valign="top"
| Select
| View
| [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]]

|- valign="top"
| Select
| Procedure
| [[dbo.uspGetEmployeeManagers_(procedure)|dbo.uspGetEmployeeManagers]]

|- valign="top"
| Select
| Procedure
| [[dbo.uspGetManagerEmployees_(procedure)|dbo.uspGetManagerEmployees]]

|- valign="top"
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeeHireInfo_(procedure)|HumanResources.uspUpdateEmployeeHireInfo]]

|- valign="top"
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeeLogin_(procedure)|HumanResources.uspUpdateEmployeeLogin]]

|- valign="top"
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeePersonalInfo_(procedure)|HumanResources.uspUpdateEmployeePersonalInfo]]

|- valign="top"
| Select
| SQL table-valued-function
| [[dbo.ufnGetContactInformation_(function)|dbo.ufnGetContactInformation]]

|}


== automatically generated ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''Table
| HumanResources.Employee
|- valign="top"
| '''Description
| Employee information such as salary, department, and title.
|-
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description / PK / Index
		
|- valign="top"
| EmployeeID
| int
| not null
| 
| Primary key for Employee records.PK_Employee_EmployeeID
|- valign="top"
| NationalIDNumber
| nvarchar(15)
| not null
| 
| Unique national identification number such as a social security number.AK_Employee_NationalIDNumber
|- valign="top"
| ContactID
| int
| not null
| 
| Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
|- valign="top"
| LoginID
| nvarchar(256)
| not null
| 
| Network login.AK_Employee_LoginID
|- valign="top"
| ManagerID
| int
| null
| 
| Manager to whom the employee is assigned. Foreign Key to Employee.MIX_Employee_ManagerID
|- valign="top"
| Title
| nvarchar(50)
| not null
| 
| Work title such as Buyer or Sales Representative.
|- valign="top"
| BirthDate
| datetime
| not null
| 
| Date of birth.
|- valign="top"
| MaritalStatus
| nchar(1)
| not null
| 
| M = Married, S = Single
|- valign="top"
| Gender
| nchar(1)
| not null
| 
| M = Male, F = Female
|- valign="top"
| HireDate
| datetime
| not null
| 
| Employee hired on this date.
|- valign="top"
| SalariedFlag
| Flag
| not null
| (1)
| Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
|- valign="top"
| VacationHours
| smallint
| not null
| (0)
| Number of available vacation hours.
|- valign="top"
| SickLeaveHours
| smallint
| not null
| (0)
| Number of available sick leave hours.
|- valign="top"
| CurrentFlag
| Flag
| not null
| (1)
| 0 = Inactive, 1 = Active
|- valign="top"
| rowguid
| uniqueidentifier
| not null
| (NEWID())
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.AK_Employee_rowguid
|- valign="top"
| ModifiedDate
| datetime
| not null
| (GETDATE())
| Date and time the record was last updated.
|}



{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Person.Contact_(table)|Person.Contact]]
| ContactID
| ContactID
|-
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
| ManagerID
| EmployeeID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column
		
|-
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
| EmployeeID
| ManagerID
|-
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
| EmployeeID
| EmployeeID
|-
| [[HumanResources.EmployeeDepartmentHistory_(table)|HumanResources.EmployeeDepartmentHistory]]
| EmployeeID
| EmployeeID
|-
| [[HumanResources.EmployeePayHistory_(table)|HumanResources.EmployeePayHistory]]
| EmployeeID
| EmployeeID
|-
| [[HumanResources.JobCandidate_(table)|HumanResources.JobCandidate]]
| EmployeeID
| EmployeeID
|-
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| EmployeeID
| EmployeeID
|-
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
| EmployeeID
| SalesPersonID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| dEmployee
| INSTEAD OF DELETE
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Dependency Type
| '''Object Type
| '''Referenced Object

|-
| Data Type
| Type
| [[dbo.Flag_(type)|dbo.Flag]]

|-
| Schema
| Schema
| [[HumanResources_(schema)|HumanResources]]

|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Reference Type
| '''Object Type
| '''Referencing Object

|-
| Select
| View
| [[HumanResources.vEmployee_(view)|HumanResources.vEmployee]]

|-
| Select
| View
| [[HumanResources.vEmployeeDepartment_(view)|HumanResources.vEmployeeDepartment]]

|-
| Select
| View
| [[HumanResources.vEmployeeDepartmentHistory_(view)|HumanResources.vEmployeeDepartmentHistory]]

|-
| Select
| View
| [[Sales.vSalesPerson_(view)|Sales.vSalesPerson]]

|-
| Select
| View
| [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]]

|-
| Select
| Procedure
| [[dbo.uspGetEmployeeManagers_(procedure)|dbo.uspGetEmployeeManagers]]

|-
| Select
| Procedure
| [[dbo.uspGetManagerEmployees_(procedure)|dbo.uspGetManagerEmployees]]

|-
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeeHireInfo_(procedure)|HumanResources.uspUpdateEmployeeHireInfo]]

|-
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeeLogin_(procedure)|HumanResources.uspUpdateEmployeeLogin]]

|-
| Update
| Procedure
| [[HumanResources.uspUpdateEmployeePersonalInfo_(procedure)|HumanResources.uspUpdateEmployeePersonalInfo]]

|-
| Select
| SQL table-valued-function
| [[dbo.ufnGetContactInformation_(function)|dbo.ufnGetContactInformation]]

|}