Need Help with datetime validation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help with datetime validation

Author

Topic

sanlen
Starting Member

29 Posts

Posted - 2008-10-20 : 04:01:47

Hi,

It is part of my assignment to use TRIGGER to validate the date from two different tables. I have table Project & table TimeCards. Before i can insert data in to date_issue in table TimeCards, it first needs to check the start_date in table Project. If the date_issue is bigger than the start_date then the process of inserting data is allow, else it would error.

I try with the following code:

CREATE TABLE Project
(
project_id VARCHAR(10) NOT NULL,
project_name VARCHAR(30) NOT NULL,
emp_id INT,
start_date DATETIME NOT NULL,
end_date DATETIME ,

CONSTRAINT pk_project_id PRIMARY KEY(project_id),
CONSTRAINT fk_emp_id FOREIGN KEY(emp_id) REFERENCES Employee(emp_id)
)

CREATE TABLE TimeCards
(
time_card_id INT IDENTITY(1,1),
emp_id INT,
date_issue DATETIME,

CONSTRAINT pk_timecardid PRIMARY KEY (time_card_id),
CONSTRAINT fk_empid_timecard FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
)

CREATE TRIGGER DateCheck ON TimeCards
FOR INSERT, UPDATE
AS

IF EXISTS((SELECT start_date FROM Project)<date_issue)
BEGIN

INSERT INTO TimeCards (time_card_id, emp_id, date_issue),

END

I just know that the way i am creating the TRIGGER is wrong, but i cannot find out how to get this done. I am the newbie. Please advise.

Thanks you very much for your time and all the advise you may give me.

Best Regard,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 04:08:36

CREATE TRIGGER DateCheck ON TimeCards
INSTEAD OF INSERT, UPDATE
AS

IF EXISTS(SELECT 1 FROM inserted WHERE date_issue <start_date )
BEGIN

RAISERROR....
--code to raise error

END
ELSE
BEGIN
--insert code
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources