Insert Trigger Question

[dgrewe]

I know just this side of nothing about MSSQL triggers, (I'm a VFP programmer) but I have to write a trigger for a client.

Some rogue program is placing a record in a table (SYINVL) with a primary key of zero. Then days later the application crashes when a second person attenpts to add a record with a value other then zero with the error Duplicate Value in Primary Key. (The rogue program could be inside my application or one of the 4 other applications maintained by a different set of programmers that inserts records into this table.)

I included the script to create the holding table so you would know the Table structure if you had any questions.
The Primary key field is SYINVLID.
In the Hold table there should not be a index.

would these scripts work ??
Or do you guys know of a better way to do it until I can find the problem program/outside application.)

\* MSSQL Create Hold Table Script
--USE LEADS GO
CREATE TABLE [dbo].[syinvl_hold] (
            [syinvlid]   [int]           NOT NULL ,
            [relrec_key] [char] (2)      NOT NULL ,
            [relrec_id]  [int]           NOT NULL ,
            [relatnship] [char] (15)     NOT NULL ,
            [date_inv]   [datetime]      NULL ,
            [inc_desc]   [char] (55)     NOT NULL ,
            [rec_key]    [char] (2)      NOT NULL ,
            [rec_id]     [int]           NOT NULL ,
            [agencyid]   [char] (4)      NOT NULL ,
	    [innameflag] [char] (1)      NOT NULL ,
            [sysgen]     [char] (1)      NOT NULL ,
            [inv_status] [char] (6)      NOT NULL ,
            [modtime]    [datetime]      NULL,
            [moduser]    [char] (11)     NOT NULL ,
            [secure]     [int]           NOT NULL ,
            [scmainid]   [int]           NOT NULL ,
            [approved]   [int]           NOT NULL ,
            [Username]   [char] (55)     NULL ,
            [Hostname]   [char] (22)     NULL ,
            [Appname]    [char] (22)     NULL ,
            [SQLstamp]   [datetime]      NULL 

) ON [PRIMARY]
GO


\* MSSQL Create insert trigger script
create Trigger syinvl_insert_hold
On SYINVL
For INSERT
As
 
IF syinvlid = 0

   INSERT INTO syinvl_hold 
	(syinvlid, relrec_key, relrec_id, relatnship, date_inv, inc_desc, rec_key, rec_id,
          agencyid, innameflag, sysgen, inv_status, modtime,  moduser,  secure, scmainid, approved,
	  username, hostname, appname, sqlstamp) 
      VALUES 
	(SELECT syinvlid, relrec_key, relrec_id, relatnship, date_inv, inc_desc, rec_key, rec_id, 
	  agencyid, innameflag, sysgen,    inv_status, modtime,  moduser,  secure, scmainid, approved,
	  SUBSTRING(SUSER_SNAME(), 1, 55), SUBSTRING(HOST_NAME(), 1, 22), SUBSTRING(APP_NAME(), 1, 22), GETDATE() from SYINVL where SYINVLID = 0)

   DELETE FROM syinvl WHERE syinvlid = 0

ENDIF 
GO

David W. Grewe Dave

 

[SQLSister]

No
you do not use the values clause with a select.

No on to how to do triggers. First there are two psuedotables called inserted and delted that are only accessible in a trigger. Inserted contains the new values, delted contains the orginal values. You use these to access the data that you want to manipulate in the trigger.

Next thing to know about triggers is to never make the assumption that only one record is being added, changed or deleted (depending onthe type of trigger it is) . Always write your code to process a group of records not just one. Do not under any circumstances do this with a cursor. I'm fixing an issue where someone did that here right now and intial tests show that an insert of 40000 records will go from 47 minutes to 32 seconds. You can see why it is critical in a trigger to avoid such performance issues.

Next issue is why on earth are you allowing people to create a numeric primary key? You should be using an indentity field (the SQL Server equivalent of autonumber) and then this would not be an issue requiring a trigger at all. If you can avoid a trigger that is best. If you can use a constraint instead of a trigger that is better. If you cannot change to autonumbering right now, can you put a constraint on the field disallowing the use of 0 for the field?

"NOTHING is more important in a database than integrity." ESquared

 

[dgrewe]

Thanks for the fast response.
on your statements 1,2,3 I have that solved now.
On item #4, the problem is this.
There are 4 seperate applications that add records to the Table. None of the applications are in the same language, One is VFP, one is .Net one is PHP/Pearl.....
Each application have it's own code/classes/
I control the VFP code, and my classes are not allowing a 0 PriKey value. I have not control over the other 3 code/classes. But one of us is doing it. I'm writting the trigger as a troubleshoot method to find the application doing it.

This is what the trigger looks like now.

IF (EXISTS(SELECT * FROM sysobjects WHERE xtype = 'TR' AND name = 'syinvl_insert_hold'))
  DROP TRIGGER syinvl_insert_hold
  GO

create Trigger syinvl_insert_hold
ON SYINVL
FOR INSERT, UPDATE
AS
  DECLARE @Inserted TABLE (syinvlid int, relrec_key char(2), relrec_id int, relatnship Char (15), date_inv datetime, inc_desc char(55), rec_key char(2), rec_id int, agencyid char(4), innameflag char(1), sysgen char(1), inv_status char(6), modtime datetime, moduser Char(11),     secure int, scmainid int, approved int)

INSERT INTO @inserted
    SELECT syinvlid, relrec_key, relrec_id, relatnship, date_inv, inc_desc, rec_key, rec_id,agencyid, innameflag, sysgen, inv_status, modtime, moduser, secure, scmainid, approved
   FROM inserted
   IF (EXISTS(SELECT * FROM @inserted WHERE syinvlid = 0) 
        AND @@TRANCOUNT > 0)
    BEGIN
        SELECT @@TRANCOUNT
        ROLLBACK
            INSERT INTO syinvl_hold
            SELECT syinvlid, relrec_key, relrec_id, relatnship, date_inv, inc_desc, rec_key, rec_id, agencyid, innameflag, sysgen, inv_status, modtime, moduser, secure, scmainid, approved, SUBSTRING(SUSER_SNAME(), 1, 55), SUBSTRING(HOST_NAME(), 1, 22), SUBSTRING(APP_NAME(), 1, 22), GETDATE()
            FROM @inserted
    END  
 GO

David W. Grewe Dave