June 24, 2011 at 9:44 am
This is a nomination for Sean:
"SQLServerCentral.com Best Quoters Award"
I guess I win by default since it is a new award and I am the only nomination. WOOT!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 9:49 am
If there are any other ideas fell free to reply. I have alreay done as Sean spoke about as far as where I stand with this type of situation with my management! Still thinking if i need to break out the duster or not!
June 24, 2011 at 10:02 am
So even a view with instead of triggers won't work in this case to completely replace the original table in the eyes of the app? (I'm aware you probably can't change the name of the table in the App, which is why you change the name of the base table and make the view (with the triggers) the name of what the table was. The app should be clueless of this change.)
Because then you could do something such as:
SELECT CASE WHEN SYSTEM_USER = 'sphelabaum' THEN 'Full SSN!' ELSE 'Partial' END SSNField
With whatever criteria you need to use, possibly the name of the Program accessing the view.
June 24, 2011 at 10:30 am
So even a view with instead of triggers won't work in this case to completely replace the original table in the eyes of the app? (I'm aware you probably can't change the name of the table in the App, which is why you change the name of the base table and make the view (with the triggers) the name of what the table was. The app should be clueless of this change.)
Because then you could do something such as:
SELECT CASE WHEN SYSTEM_USER = 'sphelabaum' THEN 'Full SSN!' ELSE 'Partial' END SSNField
With whatever criteria you need to use, possibly the name of the Program accessing the view.
Seth Phelabaum
--------------------------------------------------------------------------------
Consistency is only a virtue if you're not a screwup
Garadin,
The only problem I see with that is that you can not do For select in a trigger, I did mention that earlier.
June 24, 2011 at 11:44 am
Right, the triggers aren't for the selects, they're for the insert/update/deletes, so that they update back the original table when people take actions on your view that would normally be against the table. You can do what you need to do for selects in the view itself, you don't need a for select trigger.
June 24, 2011 at 12:00 pm
So then not sure how you suggest this might work, maybe you could give a little more detail as to how this could work.
June 24, 2011 at 12:01 pm
Maybe you are not aware that you can create updateable views? Here is an article from BOL. I think what has been somewhat referenced by all of us but NOT very explicitly is that you could create view with the name of the current table. You would have to rename your current table to some other name. Then you can control what is seen from the base table. The most important caveat is that for a view to be updateable is may ONLY reference a single base table. I can knock up an example if you need one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 12:05 pm
Sean
Sounds good but ths sounds as if it would effect the applcation? Maybe it example mght help.
June 24, 2011 at 12:09 pm
Well actually the whole idea is to have your view take over the name of your current table. Then dml statements would remain the same. Let's say your current table is MyTable. You just rename that to something else and your new view is called MyTable. That way insert into MyTable(cols) values (cols) would still work the same. Same with delete and update. Basically, you just need to create instead of triggers for insert, update and delete to perform the insert, update or delete on your base table. The advantage in your case is in the view definition you can mask your column. It will take a bit to mock one up but i just got back from a huge bbq lunch and dont really feel all that much like working. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 12:17 pm
fleshing out a rough example from Sean and Garadin:
--proof of concept:
--unlike your developers, do not do this work on production:
USE tempdb;
CREATE TABLE [dbo].[PERSON1](
[HMY] [numeric](18, 0) NOT NULL, ---- this is an ID row assigned by app
[ULASTNAME] [varchar](256) NULL,
[SFIRSTNAME] [varchar](255) NULL,
[SADDR1] [varchar](50) NULL,
[SADDR2] [varchar](50) NULL,
[SCITY] [varchar](40) NULL,
[SSTATE] [varchar](5) NULL,
[SZIPCODE] [varchar](12) NULL,
[SFEDID] [varchar](15) NULL -- does not have the same name in every table but is a SSN
) ;
GO
INSERT INTO [dbo].[PERSON1] VALUES ('1629','Ying','Chris','3776 Main Street 227','','Brooklyn','NY' ,'36501' ,'123456789');
INSERT INTO [dbo].[PERSON1] VALUES ('2352','Hawk','Don','13836 Alamo Memorial Pkwy 310','','San Antonio','TX' ,'78201' ,'234-56-7890');
INSERT INTO [dbo].[PERSON1] VALUES ('9356','Cox','Ed','8803 North 7 Highway','Building 6','Kansas City','MO' ,'64106' ,'345-67-8901');
INSERT INTO [dbo].[PERSON1] VALUES ('1981','Ainey','Adam','4836 Alamo Memorial Pkwy 192','','San Antonio','TX' ,'78201' ,'456-78-9012');
INSERT INTO [dbo].[PERSON1] VALUES ('6392','Talon','Wilson','6732 Main St','Building 12','Kansas City','MO' ,'64102' ,'567890123');
--Step 1: rename the table
EXEC sp_rename 'dbo.PERSON1','PERSON1ORIG'
--step 2: Create the Replacement View
GO
CREATE VIEW [dbo].[PERSON1]
AS
SELECT
HMY,
ULASTNAME,
SFIRSTNAME,
SADDR1,
SADDR2,
SCITY,
SSTATE,
SZIPCODE,
CASE
WHEN APP_NAME() like 'MyAppName%'
THEN SFEDID
--developers are connecting with SQL server or something...hide the data
ELSE LEFT(SFEDID,5) + 'XXXX'--wouldn't 9 x's be better? Or NULL?
END AS SFEDID
FROM PERSON1ORIG
GO
--STEP 3: Create an instead of trigger
CREATE TRIGGER TR_PERSON1 ON [dbo].[PERSON1]
INSTEAD OF INSERT,UPDATE
AS
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
UPDATE PERSON1ORIG
SET PERSON1ORIG.ULASTNAME = INSERTED.ULASTNAME,
PERSON1ORIG.SFIRSTNAME = INSERTED.SFIRSTNAME,
PERSON1ORIG.SADDR1 = INSERTED.SADDR1,
PERSON1ORIG.SADDR2 = INSERTED.SADDR2,
PERSON1ORIG.SCITY = INSERTED.SCITY,
PERSON1ORIG.SSTATE = INSERTED.SSTATE,
PERSON1ORIG.SZIPCODE = INSERTED.SZIPCODE,
PERSON1ORIG.SFEDID = INSERTED.SFEDID
FROM INSERTED
WHERE PERSON1ORIG.HMY = INSERTED.HMY
--no updates allowed from the developers!
AND APP_NAME() LIKE 'MyAppName%'
END
ELSE
BEGIN
INSERT INTO PERSON1ORIG( HMY,ULASTNAME,SFIRSTNAME,SADDR1,SADDR2,SCITY,SSTATE,SZIPCODE,SFEDID)
SELECT HMY,ULASTNAME,SFIRSTNAME,SADDR1,SADDR2,SCITY,SSTATE,SZIPCODE,SFEDID
FROM INSERTED
WHERE APP_NAME() LIKE 'MyAppName%'
END
Lowell
June 24, 2011 at 12:21 pm
Excellent. Thanks Lowell. Of course now I have no excuse not to work this afternoon. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 12:23 pm
Sean Lange (6/24/2011)
Excellent. Thanks Lowell. Of course now I have no excuse not to work this afternoon. 😛
there's always work to do in the salt mines...i'm sure. I wanted you to have a chance to digest all that BBQ.
Lowell
June 24, 2011 at 12:26 pm
One thing to keep in mind. You mentioned that this is a third party app and that you will have periodic updates. You will probably want to create a script to undo your renaming for product upgrades in case the update needs to change the underlying structure for some reason. You will need to monitor VERY closely when system upgrades occur to make sure your view will return all the columns in needs to. And keep in mind that if you code your view as select * it will NOT start returning new columns in the base table. If the underlying structure changes you will need to rebuild your view. Not a big deal but knowing about up front may save you a few late nights pulling out your hair.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 12:29 pm
Sean Lange (6/24/2011)
Excellent. Thanks Lowell. Of course now I have no excuse not to work this afternoon. 😛
Heh, well, I was watching my other monitor coming up with my own version of a how/to example, so here it is (although I believe Lowell's covered it).
USE tempdb
-- This is your base table
CREATE TABLE A (
EmpID int PRIMARY KEY CLUSTERED,
ssn varchar(30),
NAME varchar(100)
)
GO
-- This is your populated base table
INSERT INTO A (EmpID, ssn, Name)
SELECT 1,'123-45-6789','Bob' UNION ALL
SELECT 2,'234-56-7890','Joe'
GO
-- This is you renaming your base table
sp_rename 'A','A_Old','object'
GO
-- This is your updatable view (Instead of Triggers might be necessary)
CREATE VIEW A
AS
SELECT
EmpID,
CASE WHEN SYSTEM_USER = 'SomebodyElse' THEN SSN ELSE 'XXX-XX-' + RIGHT(SSN,4) END SSN,
Name
FROM A_Old
GO
-- Test the view Select
SELECT * FROM A
-- Update the View
UPDATE A
SET SSN = '123-45-678A'
WHERE EmpID = 1
GO
-- Error: (And why you may need an Instead of Trigger)
--Msg 4406, Level 16, State 1, Line 6
--Update or insert of view or function 'A' failed because it contains a derived or constant field.
-- Create an Instead of Trigger for Updates
CREATE TRIGGER TR_XU_A ON A
INSTEAD OF UPDATE
AS
UPDATE A_Old
SET SSN = I.SSN,
Name = I.Name
FROM A_Old
INNER JOIN INSERTED I ON A_Old.EmpID = I.EmpID
GO
-- Update the View (Again)
UPDATE A
SET SSN = '123-45-678A'
WHERE EmpID = 1
GO
-- Select from the view again
SELECT * FROM A
GO
-- Make sure the base table data is correct
SELECT * FROM A_Old
DROP VIEW A
DROP TABLE A_Old
June 24, 2011 at 12:32 pm
Garadin (6/24/2011)
Heh, well, I was watching my other monitor coming up with my own version of a how/to example, so here it is (although I believe Lowell's covered it).
Yeah I was pretty much doing my own cooked version and after Lowell posted his I just deleted mine. As usual his was spot on and actually using the structure from the OP. Also as usual mine was vague and totally off topic. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply