Need Help With Trigger - SQL Server Forums

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 Trigger

Author

Topic

sanlen
Starting Member

29 Posts

Posted - 2008-11-17 : 20:53:02

Hi All,

I have two tables as below. I want to ensure that the date_issue in table TimeCards is greater than the current date and start_date in table Project before the data can be entered into table TimeCards. I tried with the below codes. It seems ok when i run it, but while enter the data into table TimeCards, then seem nothing happen even the date_issue is greater than current date and start_date. Could you please advise?

Thanks you very much for your time.

---------------------------------------

CREATE TABLE Project
(
project_id VARCHAR(10), --Must be unique & in P[0-9][0-9][0-9] formatted
project_name VARCHAR(30) NOT NULL,
project_desc VARCHAR(50),
client_id INT,
emp_id INT,
[start_date] DATETIME NOT NULL,
end_date DATETIME NOT NULL, --Project end date must be greater than project start date
billing_est MONEY, --Billing Estimation must be greater than 1000

CONSTRAINT ck_billing_est CHECK(billing_est > 1000),
CONSTRAINT ck_datesequence CHECK(end_date>[start_date]),
CONSTRAINT pk_project_id PRIMARY KEY (project_id),
CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client(client_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, --Greater than current date & project start date which employee assigned

CONSTRAINT ck_date_issue CHECK (date_issue>getdate()),
CONSTRAINT pk_time_card_id PRIMARY KEY (time_card_id),
CONSTRAINT fk_emp_id_TimeCards FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
)

--------------------------------------------

CREATE TRIGGER trg_date_issue_check
ON TimeCards INSTEAD OF INSERT, UPDATE
AS
DECLARE @emp_id INT
DECLARE @project_start_date DATETIME
DECLARE @timecards_issue_date DATETIME

SET @project_start_date = (SELECT start_date FROM Project WHERE @emp_id = emp_id)

IF (@timecards_issue_date > getdate()) AND (@timecards_issue_date > @project_start_date)
INSERT INTO TimeCards (emp_id, date_issue) VALUES (@emp_id, @timecards_issue_date)

ELSE
PRINT 'Issue date must be bigger than current date & project start date'

----------------------------------------

Best Regard,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 22:51:49

i think what you need is a check constraint on issue_date column to check if its greater than current date and start_date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 22:57:04

something like this

create table TimeCards 
(
id int identity(1,1)PRIMARY KEY CLUSTERED,
val varchar(10),
sdate datetime,
date_issue datetime 
)
GO
ALTER TABLE TimeCards ADD CONSTRAINT CHK_IDATE CHECK (date_issue > sdate and idate>getdate())

sanlen
Starting Member

29 Posts

Posted - 2008-11-17 : 22:59:26

Actually, this is part of my assignments, and it is compulsory that i have to apply this issue with TRIGGER. Could you please advise?

Please apologize for any inconvenience.

Thanks

Best Regard,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 23:02:38

and if you need trigger, you want this

CREATE TRIGGER trg_date_issue_check
ON TimeCards INSTEAD OF INSERT, UPDATE
AS

IF EXISTS(
SELECT 1
FROM Project p 
INNER JOIN INSERTED i
ON i.emp_id = p.emp_id
AND (i.date_issue< p.start_date
OR i.date_issue <GETDATE()))
RAISEERROR 'Issue date must be bigger than current date & project start date',10,1

ELSE
INSERT INTO TimeCards (emp_id, date_issue) 
SELECT emp_id, date_issue FROM INSERTED
GO

sanlen
Starting Member

29 Posts

Posted - 2008-11-18 : 02:04:03

Hi,

Thanks you very much. However, could you also please advise what does SELECT 1 mean??

Thanks

Best Regard,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 03:16:00

it just returns a status value to indicate presence of records or not

sanlen
Starting Member

29 Posts

Posted - 2008-11-18 : 21:31:03

Hi,

Thanks you very much for your advise. It's really helpful for me. I now have another problems that also need your help.

I have Table1 wich has fieldA, fieldB, and fieldC. I want to generate all the records in that table to a text file (*.txt), could you please advise how can i get this done?

Thanks you very much for your time.

Best Regard,
SANLEN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 22:44:25

quote:
Originally posted by sanlen

Hi,

Thanks you very much for your advise. It's really helpful for me. I now have another problems that also need your help.

I have Table1 wich has fieldA, fieldB, and fieldC. I want to generate all the records in that table to a text file (*.txt), could you please advise how can i get this done?

Thanks you very much for your time.

Best Regard,
SANLEN

use bcp or OPENROWSET or Export Import wizard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources