Help with my 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with my trigger

Author

Topic

JonBlack
Starting Member

1 Post

Posted - 2005-09-23 : 10:47:58

I'm fairly new to writing triggers and it's giving me a beatdown. What I'm trying to do is create a single trigger that does the following. 1) Corrects the common mistakes of our users input, which mostly consists of adding erroneous spaces. Then creating the data for two columns from the previous columns' data entered. I will also add the upper(text) later on. 2) To check to a column to verify that the entry is on our master list.

I have been able to make 1 & 2 work as separate triggers. When I try to combine them into one trigger it appears that everything runs simultaneously and gives me an error.

Can someone help?

Thanks,
JB

CREATE TRIGGER SNC ON [DATA].[a44]
FOR INSERT, UPDATE
AS
UPDATE DATA.a44
/* THIS IS PART 1 OF MY CODE */
SET COL1 = rtrim(COL1)
UPDATE DATA.a44
SET COL1 = ltrim(COL1)
UPDATE DATA.a44
SET COL2 = rtrim(COL2)
UPDATE DATA.a44
SET COL2 = ltrim(COL2)
UPDATE DATA.a44
SET COL3= rtrim(COL3)
UPDATE DATA.a44
SET COL3 = ltrim(COL3)
UPDATE DATA.a44
SET COL4 = rtrim(COL4)
UPDATE DATA.a44
SET COL4 = ltrim(COL4)
UPDATE DATA.a44
SET COL5 = rtrim(COL5)
UPDATE DATA.a44
SET COL5 = ltrim(COL5)
UPDATE DATA.a44
SET COL6 = COL2 + ' ' + COL3 + ' ' + COL4
UPDATE DATA.a44
SET COL6 = rtrim(COL6)
UPDATE DATA.a44
SET COL6 = ltrim(COL6)
UPDATE DATA.a44
SET COL7 = COL1+ ' ' + COL6+ ' ' + COL5
UPDATE DATA.a44
SET COL7 = rtrim(COL7)
UPDATE DATA.a44
SET COL7 = ltrim(COL7)
/* THIS IS PART 2 OF MY CODE */
IF (select count (*) from a37) = 0
BEGIN
IF (select count (*) from master_list, inserted
WHERE master_list.COL6 = inserted.COL6) = 0
BEGIN
RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
END
END
ELSE
IF (select count (*) from master_list, a37, inserted
WHERE master_street_list.COL6 = inserted.COL6 or a37.COL6 = inserted.COL6) = 0
BEGIN
RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
END

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 11:03:28

Some suggestions:


UPDATE U
SET COL1 = ltrim(rtrim(COL1)),
    COL2 = ltrim(rtrim(COL2)),
...
FROM DATA.a44 AS U
     JOIN inserted AS I
         ON I.MyPK = U.MyPK

-- When you have a million rows in a37 this will take ages !!!
IF (select count (*) from a37) = 0
IF NOT EXISTS (SELECT * FROM dbo.a37)
BEGIN
    IF NOT EXISTS 
    (
        SELECT *
        FROM   dbo.master_list AS ML
-- Do not use "inserted" here - its data has not been cleaned up / trimmed
               [s]JOIN inserted[s/]
               JOIN DATA.a44
                   a44.COL6 = ML.COL6
    )
    BEGIN
        RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
    END
END
ELSE
...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources