Help with Transact-SQL Trigger or Condition

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Transact-SQL Trigger or Condition

Author

Topic

toniolo
Starting Member

14 Posts

Posted - 2007-06-02 : 23:17:54

Hello,
I have limited knwoledge in TRANSACT-SQL and would like to ask someone's assistance in a problem I have.

Please let me explain what I need.

I have an application that runs on MSDE2000.

This application issues some labels for customers and every day I have to enter a new code on the application that must be valid for only the same day. I can use more than one code on the same day, but NEVER on different days.

What happens at the moment is that sometimes the operator forgets to change the code and the same code is used in more than one day causing a lot of problems.

The question is. How can I lock the database so the code can be used in ONLY one day ?

Let me give you an example:

First Day

Name Date Code Label#
John 06/01 1234 101
Mark 06/01 1234 102
Mary 06/01 1234 103

Following Day (note that more than one code can be used on the same day):

Name Date Code Label#
John 06/02 5678 104
Mark 06/02 5678 105
Mary 06/02 7789 106

Third day (Please note the mistake. The operator forgot to change the Code on the application and the same code has been used on 2 different days):

Name Date Code Label#
John 06/03 7789 107
Mark 06/03 7789 108
Mary 06/03 7789 109

I believe that this could be solved with a TRIGGER. Every time a new label is generated it checks to see if the Code has been used on the previous day.

Not sure if this is the correct way though.

I would appreciate any help or idea.

Thank you very much.

Marcelo

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-03 : 00:36:28

I don't think you could use a check constraint since a check costraint can't depend on values already present in the table (can it?)

I think you have to use a trigger, maybe something like this:


create trigger mytrigger
on mytable
for insert, update
as
if exists (select * from inserted i join mytable t on i.Code = t.Code and datediff(day, i.date, t.date) = 0)
begin
   raiserror ('code already exists for that day', 16, 1)
   rollback transaction
end

edit: typos

www.elsasoft.org

toniolo
Starting Member

14 Posts

Posted - 2007-06-03 : 02:16:40

Hi Jesse, thanks a lot for your help. I believe we are very close to the solution.

I tried the proposed code above and got the error: "Incorrect Syntax Near the keyword AND).

I am not familiar with DATEDIFF, however I have searched for its meaning and found out that DATEDIFF = Returns the number of date and time boundaries crossed between two specified dates.

Could you please just help me to adapt the date format I have.
The format on my table is a date like "2007-05-21 15:04:54.000"

Thanks
Marcelo

toniolo
Starting Member

14 Posts

Posted - 2007-06-03 : 08:20:30

Hi again Jesse.
I have been working on this project for hours and I believe that, with your help, I am very close to find a solution.

Could you please just give me some direction here ?

Maybe I can simplify things and would like to just give you some more information:

All data is stored in a table called HISTORY. New data are stored in a table called CURRENT that is moved to HISTORY at the end of day. Therefore, the table CURRENT is always empty at the beggining of the day.

I don't think it will be necessary to run a DATE validation as if the fist entry already contains an existing code then the operator must change it to a new one. All the following entries will be fine as the data is not moved to HISTORY until the end of the day.

Maybe the best thing to do is to tell SQL to compare the CODE entered in the first time the operator types a new entry. If the CODE is not in HISTORY that's OK..go ahead. If the CODE is already in HISTORY then the error message comes up.

I just need your help in creating this Trigger as:

CREATE TRIGGER CHECKCODE
ON Current
FOR INSERT, UPDATE
AS
IF EXISTS (Select Code FROM Current ????????????????????) <<Compare with Codes already in HISTORY
BEGIN
RAISERROR ('This Code already Exist, 16,1)
ROLLBACK TRANSACTION
END

Thank you very much again.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-03 : 22:55:56

in that case I think it would be:


CREATE TRIGGER CHECKCODE
ON Current
FOR INSERT, UPDATE
AS
IF EXISTS (select * from inserted i join HISTORY h on h.Code=i.Code)
BEGIN
RAISERROR ('This Code already Exist, 16,1)
ROLLBACK TRANSACTION
END

www.elsasoft.org

toniolo
Starting Member

14 Posts

Posted - 2007-06-04 : 00:44:55

Hi Jesse,
I just tried it and it works. Thank you so much for that. You saved the day !!!
Would you mind if I ask you an additional thing about the TRIGGER above ?
I have tried myself but I am unable to find the correct syntax for this change.

The Scenario is:

How about the same condition above, the CODE can't exist in HISTORY however ONLY for Names = John.

I know there should be an "AND" at the end of the IF EXISTS but I have been getting "Ambiguous Column name" error.

Thanks again.
Marcelo

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-04 : 01:21:43

you just need to prefix the column name:

select * from inserted i join HISTORY h on h.Code=i.Code and i.Name='John'

You might do well to learn sql a little more though - regulars here seem to post these three tutorials a lot - I haven't gone through them myself though:

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

www.elsasoft.org

toniolo
Starting Member

14 Posts

Posted - 2007-06-04 : 04:09:56

Hi Jesse, it seems to be working.
Thanks once more.
As I said before, I am just a begginer in SQL. I have a quite good knowledge of MS Access but just started working with SQL.
Thanks for the references with tutorials about SQL>
If you ever need anything from New Zealand just let me know.
Marcelo

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-04 : 10:51:40