Trigger Help Needed

[annethorne]

Hi,

I have never written a trigger before. This is probably very simply, but I need some assistance:

The basic concept is as follows. When user puts values into Table One, a TRIGGER occurs and values are automatically put into Table Two:

Table One has 3 basic columns, and Table Two has 2 basic columns.

Table One
NumStart: 1
NumEnd: 3
Code: A

Table Two
Num: 1
Code: A

Num: 2
Code: A

Num: 3
Code: A


Thank you in advance for any help you can give.
Anne

 

[gmmastros]

Is this for that ZIP CODE thing? Here's my suggestion.

Since you are working with zipcodes, I would suggest you create a 'table2' that has every zipcode in it. Then, for un-specified zip codes, set the 'code' field to NULL. Make sure the zipcode is the primary key with a clustered index (to help with any possible performance issues). A zipcode table is relatively small, so there shouldn't be any performance issues. This will simplify the trigger because you will only have to worry about updating Table2 (based on table 1).

I suggest you create a Zip Code Detail table like so...

Create        
Table  ZipCodeDetail 
       (
       ZipCode Integer Primary Key Clustered, 
       Code VarChar(10)
       )

Then, populate it. This may take a minute or 2 but it only has to be done once, so you shouldn't worry about it.

Declare @i Integer
Set @i = 1

While @i < 100000
  Begin
    Insert Into ZipCodeDetail(ZipCode) Values(@i)
    Set @i = @i + 1
  End

Then, create a trigger on the zipcode table, like so...

CREATE TRIGGER ZipCode_Trigger ON [dbo].[ZipCode] 
FOR INSERT, UPDATE, DELETE 
AS
Update ZipCodeDetail
Set       Code = NULL
From    ZipCodeDetail
            Inner Join Deleted On ZipCodeDetail.ZipCode Between Deleted.MinZipCode And Deleted.MaxZipCode

Update	ZipCodeDetail
Set	Code = Inserted.Code
From	ZipCodeDetail
	Inner Join Inserted On ZipCodeDetail.ZipCode Between Inserted.MinZipCode And Inserted.MaxZipCode

When a record is deleted from the zipcode table, the zipcode detail will be updated so that the code field is changed to null

When a record is inserted in to the zip code table, the zipcode detail records will be updated with whatever code you specified.

When a record is updated, it will first set the code (in the details table) to NULL, then set them to the new value.

If you want the trigger to handle inserting records, deleting records, and updating records in the detail table, it will become much more complicated.

Make sure you test this really well before putting it in to production.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[annethorne]

Thank you so much George

Yes it is for the zip code thing

I really appreciate your help
Anne