Basic Trigger Troubles! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Basic Trigger Troubles!

Author

Topic

Tekno7777
Starting Member

4 Posts

Posted - 2005-05-13 : 13:09:35

Hey i'm very new to triggers.
I have a table that contains a customer id (a primary key) and a
customer phone number, and the date it was entered

I want a trigger that does not allow the user to enter a customer if it was already entered. My trigger is as follows:
(Please read below for my symptoms)


CREATE TRIGGER tbl_cust_PreventDupeInsert
ON dbo.tbl_cust
INSTEAD OF INSERT
AS
 IF EXISTS
   (
    select t.* from tbl_cust t, inserted i 
     WHERE  i.id not in (select id from tbl_cust)
     AND
       i.leaddate = t.leaddate
     AND 
       i.daytimephone = t.daytimephone
    )
  begin
    raiserror('This lead has already been inserted for today',16,1) 
    rollback tran  
  end

If I run the select statement above as a query, it actually works. (I made a mock insert table that contained all the rows the original table did, with the addition of the new row to *be* added.)
It basically behaved the right way.
The problem is, when I assign the above trigger to the table,
and it tries to insert a customer that hasn't been put in for the day yet, it just says 'execution completed' or whatever. It should be saying 'inserted 1 row into table'
Oddly enough, if i try to insert a customer that has already been added, it gives me the error message I wanted.
Does anything have to be written above like ELSE COMMIT TRANS? anyone have a suggestion?

Regards,
Mike

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 13:21:45

Post the table DDL

BUT!

I imagine that CustomerID is an IDENTITY column, so what's the point?

You'll never know.

In any event, the way that you identify what you consider a customer that was already enetered can be simply a primary or a unique index.

No need for a trigger for this.

Brett

8-)

Tekno7777
Starting Member

4 Posts

Posted - 2005-05-13 : 13:30:57

Well, we get a lot of customers who might re-apply the next day. If they *do* re-apply, we need to keep them in the table :(
If they re-apply the same day, however, we cannot re-add them.

Any ideas on how to situate that?

Regards,
mike

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 13:33:07

Forget the technology...

Tell me how you would know they reapplied the same day....

Brett

8-)

Tekno7777
Starting Member

4 Posts

Posted - 2005-05-13 : 13:52:18

Heh..make id, daytimephone, date a key?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 15:17:00

What is id?

Please post the table DDL

problem with date, is that date is datetime....

Brett

8-)