July 3, 2012 at 3:49 am
If I have a scalar function tied to a constraint, and it selects data from the table, is the inserted data visible or does it have to be passed as a parameter.
Pseudo-code example:
MyFunction(@SomeData)
If @SomeData <> 1 THEN return 0 --I know this works
vs.
If SELECT M.SomeData FROM MyTableInsert M <> 1 return 0
--obviously I would need a where clause in the latter, but just curious if the inserted/dirty data is present in the table at the time the function fires.
Thanks for any input. I did some due diligence and could setup a test, but I'm oh so tired...and could use a definitive answer sooner rather than later.
July 3, 2012 at 5:20 am
The explanation given is not very clear
If you can explain more about your problem, we can even suggest you better solutions
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 3, 2012 at 5:25 am
maybe you just need help constructing the WHERe clause?
SELECT
M.SomeData
FROM MyTableInsert M
WHERE @SomeData <> 1
so the above, isntead of returning zero, will return an empty recordset instead, or the desired records?
Lowell
July 3, 2012 at 11:53 am
Bruce Hendry (7/3/2012)
If I have a scalar function tied to a constraint, and it selects data from the table, is the inserted data visible or does it have to be passed as a parameter.Pseudo-code example:
MyFunction(@SomeData)
If @SomeData <> 1 THEN return 0 --I know this works
vs.
If SELECT M.SomeData FROM MyTableInsert M <> 1 return 0
--obviously I would need a where clause in the latter, but just curious if the inserted/dirty data is present in the table at the time the function fires.
Thanks for any input. I did some due diligence and could setup a test, but I'm oh so tired...and could use a definitive answer sooner rather than later.
It is not present in the table, but it is passed into the function. That is the point, it needs to be present to be validated but is not committed to the table until it passes the test of the constraint.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2012 at 2:05 pm
Passed into the function as an in memory representation, like a trigger, e.g. select SomeData from inserted? Or passed in explicitly via parameters when calling the function? Thanks.
July 3, 2012 at 2:19 pm
OK your description is still incredibly vague but I think that you are asking if you call a scalar function from within a trigger can you see the newly inserted data?
The answer is it depends. You COULD add some isolation or query hints to view this from within your scalar function but that is not recommended. For that matter a scalar function inside a trigger can be really bad for performance.
Perhaps if you can explain that actual situation instead of implementation type questions we can help guide you to a better solution.
_______________________________________________________________
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/
July 3, 2012 at 2:22 pm
Bruce Hendry (7/3/2012)
Passed into the function as an in memory representation, like a trigger, e.g. select SomeData from inserted? Or passed in explicitly via parameters when calling the function? Thanks.
It's there, but not committed. The inserted table is not part of any of this.
A dirty read is relevant to think about though as a select using NOLOCK or READ UNCOMMITTED iso level would pick up your change, as would a select happening in the same transaction which is legitimate, e.g. from a function used in a check constraint:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.MyTableInsert')
AND type IN (N'U') )
DROP TABLE dbo.MyTableInsert;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.MyTableInsertConstraint')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.MyTableInsertConstraint;
GO
---------------------------------------------------------------------------------
CREATE FUNCTION dbo.MyTableInsertConstraint (@MyTableInsertID INT)
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT *
FROM MyTableInsert
WHERE MyTableInsertID = @MyTableInsertID
AND M = N )
RETURN 1
RETURN 0
END
GO
CREATE TABLE dbo.MyTableInsert
(
MyTableInsertID INT IDENTITY(1, 1)
NOT NULL,
M INT,
N INT,
SomeData VARCHAR(100),
CONSTRAINT [ck_MyConstraint] CHECK (dbo.MyTableInsertConstraint(MyTableInsertID) = 1)
);
GO
---------------------------------------------------------------------------------
-- works because M = N meaning the data is available in
-- the table otherwise we would have found no row
-- where MyTableInsertID = @MyTableInsertID
INSERT INTO dbo.MyTableInsert
(M, N, SomeData)
VALUES (1, 1, 'SomeData');
SELECT *
FROM dbo.MyTableInsert
GO
-- does not work because M != N
INSERT INTO dbo.MyTableInsert
(M, N, SomeData)
VALUES (1, 2, 'SomeData');
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2012 at 5:45 pm
Perfect, exactly what I was trying to understand. Thanks opc et al.!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply