March 18, 2008 at 12:40 pm
So if you got this far hopefully I explained it correctly. Basically I am trying to normalize a non-normalized database. The current application does insert one row at a time BUT I want to make sure that any tools clients may have created will work with recordsets instead of assuming one row or even protecting it. The basic overview is new tables that are normalized contain the data and views are created in place of the original table structure. A trigger is created on the view to handle ins/upd/del to manipulate the normalized tables. Code example
SET NOCOUNT ON
IF OBJECT_ID('vDeNorm') IS NOT NULL
BEGIN
DROP VIEW vDeNorm
DROP TABLE tblPrs
DROP TABLE tblAdr
END
GO
CREATE TABLE tblAdr(
idINT IDENTITY PRIMARY KEY,
zipCHAR(5)
)
GO
CREATE TABLE tblPrs(
idINT IDENTITY,
NAMEVARCHAR(50),
AdrIdINT,
CONSTRAINT fkPrsAdrId FOREIGN KEY(AdrId) REFERENCES tblAdr(id)
)
GO
CREATE VIEW vDeNorm AS
SELECT NAME, zip
FROM tblPrs
LEFT OUTER JOIN tblAdr ON tblAdr.id = tblPrs.AdrId
GO
CREATE TRIGGER trgDeNorm
ON vDeNorm
instead OF INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@cnt INT,
@id INT,
@rt INT,
@LastId INT
INSERT INTO tblAdr(zip)
SELECT zip FROM inserted
SET @cnt = @@ROWCOUNT
SET @id = SCOPE_IDENTITY()
SET @rt = @id - @cnt + 1
SET @LastId = IDENT_CURRENT( 'tblPrs' ) + 1
PRINT @cnt
PRINT @id
PRINT @rt;
PRINT @LastId;
INSERT INTO tblPrs(NAME, AdrId)
SELECT inserted.NAME, @id
FROM inserted
SET NOCOUNT OFF
END
GO
--Assuming there are other address from other tables on file. Don't want the id's to match
INSERT INTO tblAdr(zip)
SELECT '11111' UNION ALL
SELECT '22222' UNION ALL
SELECT '33333'
GO
--Fire the insert to the view and 'normalize' the data
INSERT INTO vDeNorm(NAME, zip)
SELECT 'Name1 with zip 44444', '44444' UNION ALL
SELECT 'Name2 with zip 55555', '55555'
GO
SELECT *
FROM tblAdr
SELECT *
FROM tblPrs
GO
SET NOCOUNT OFF
GO
I want to insert the correct foreign keys in tblPrs for the address. I've put some ideas in I was going with such as the last identity, number of rows, etc but can't find a way to do this. Any ideas please.
There is always a possibility of the same address so field comparisons aren't likely. This is for SQL2k5 but am only really familiar with SQL2k so run with it.
March 18, 2008 at 3:03 pm
I'm not sure what you're trying to accomplish. Are this your real table layouts or just a simplified structure for illustration?
If (6,'44444') is inserted to vDeNorm, its trigger inserts another row for zip '44444' into tblAdr so the data's still denomalized. Shouldn't the trigger only add rows if the value is not already present in the table?
And if you really are planning on making a table of zip codes, why not just use .zip as the key?
March 19, 2008 at 7:41 am
I was trying to keep the example to a minimum. The tblAdr table has more columns and it's posible for people/companies/banks/etc to have the same mailing address. The tblPrs is for people but the adr table contains addresses for all the enitities and more. I can make a more elaborate example but am trying a small scale example before I implement a routine. So far I have been looking at using the pk in the parent with a new column set at zero that gets updated by one of Jeff's running total updates to the temp table. It's working but want to remove the identity_insert incase of mutliple entries but just haven't finished yet.
March 19, 2008 at 8:28 am
Keith -
The trigger as written is going to give you headaches. The way it's written - you're presuming that the insert consists of only one record, which is always a dangerous assumption. Remember - triggers fire once per operation, not once per row inserted. If your insert happens to have 2 or more rows....BOOM
I'd recommend you look at the OUTPUT clause. It will give you "back" the rows you inserted WITH the newly assigned ID's.
So you could do soemthing like (aircode warning):
--create an empty table
CREATE TABLE #tblAdr(
id INT,
zip CHAR(5)
)
INSERT INTO tblAdr(zip)
OUTPUT inserted.* into #tblAdr --the temp table now has the inserted rows WITH the ID's
SELECT zip FROM inserted
--now that you have the ID's - do the second
INSERT INTO tblPrs(NAME, AdrId)
SELECT inserted.NAME, #tblAdr.ID
FROM inserted
inner join #tblAdr on inserted.ZIP=#tblAdr.Zip
Drop table #tblAdr
No fuss, no muss.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 19, 2008 at 8:40 am
Awsome, the OUTPUT clause. As I said it was always 2000 development for me. I did get it to work for any SQL2K developers as follows which is the actual table structure
CREATE TRIGGER tNzeEft
ON aba
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @action CHAR(1)
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
SET @action = 'U'
ELSE IF EXISTS(SELECT * FROM inserted)
SET @action = 'I'
ELSE IF EXISTS(SELECT * FROM deleted)
SET @action = 'D'
IF @action IS NULL
RETURN
IF @action = 'I' OR @action = 'U' BEGIN
IF EXISTS(
SELECT *
FROM inserted ins
LEFT OUTER JOIN cLbl ON ListId = 2129 AND code = RTRIM(ins.eft_id_type)
WHERE cLbl.id IS NULL
)BEGIN
RAISERROR('Type id not found',16,1)
RETURN
END
END
IF @action = 'I' BEGIN
DECLARE @AdrId INT
SELECT ins.*, 0 AdrId INTO #tmp FROM inserted ins WHERE 1=2
CREATE CLUSTERED INDEX idx ON #tmp(unique_id)
INSERT INTO #tmp SELECT ins.*, 0 FROM inserted ins ORDER BY unique_id
INSERT INTO eAdr(line1, line2, city, StateProv, zip)
SELECT RTRIM(#tmp.street1), RTRIM(#tmp.street2), RTRIM(#tmp.city), RTRIM(#tmp.st_prov), RTRIM(#tmp.zip_code)
FROM #tmp
SET @AdrId = SCOPE_IDENTITY() - @@ROWCOUNT
UPDATE #tmp SET
AdrId = AdrId + @AdrId,
@AdrId = @AdrId + 1
FROM #tmp WITH(INDEX(idx),TABLOCK)
INSERT INTO eEft(code, name, EftTypeId, AdrId)
SELECT RTRIM(#tmp.aba_num), RTRIM(#tmp.name), cLbl.id, #tmp.AdrId
FROM #tmp
INNER JOIN cLbl ON cLbl.ListId = 2129 AND code = RTRIM(#tmp.eft_id_type)
END ELSE IF @action = 'U' BEGIN
UPDATE eAdr SET
line1 = RTRIM(ins.street1),
line2 = RTRIM(ins.street2),
city = RTRIM(ins.city),
StateProv = RTRIM(ins.st_prov),
zip = RTRIM(ins.zip_code)
FROM eAdr
INNER JOIN eEft ON eEft.AdrId = eAdr.id
INNER JOIN inserted ins ON ins.unique_id = eEft.id
UPDATE eEft SET
code = RTRIM(ins.aba_num),
name = RTRIM(ins.name),
EftTypeId = cLbl.id
FROM eEft
INNER JOIN inserted ins ON ins.unique_id = eEft.id
INNER JOIN cLbl ON cLbl.ListId = 2129 AND cLbl.code = RTRIM(ins.eft_id_type)
END ELSE IF @action = 'D' BEGIN
DELETE FROM eEft
FROM eEft
INNER JOIN deleted del ON del.unique_id = eEft.id
END
END
GO
But I will definitely change it to 2k5. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply