-- =============================================
-- テーブル作成
-- =============================================
CREATE TABLE [dbo].[M_EMPLOYEE](
[EMPLOYEE_CODE] [char](5) COLLATE Japanese_CI_AS NOT NULL,
[EMPLOYEE_NAME] [varchar](20) COLLATE Japanese_CI_AS NULL,
[UPDATE_TIME] [datetime] NOT NULL CONSTRAINT [DF_M_EMPLOYEE_UPDATE_TIME] DEFAULT (getdate()),
CONSTRAINT [PK_M_EMPLOYEE] PRIMARY KEY CLUSTERED (
[EMPLOYEE_CODE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-- =============================================
-- UPDATE時、更新日時を自動登録するトリガ作成
-- =============================================

CREATE TRIGGER [TRG_M_EMPLOYEE]
ON [dbo].[M_EMPLOYEE]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

/*更新日時をセットする*/
update M_EMPLOYEE
set M_EMPLOYEE.UPDATE_TIME = GETDATE()
from INSERTED
where INSERTED.EMPLOYEE_CODE = M_EMPLOYEE.EMPLOYEE_CODE

END