August 22, 2018 at 10:07 am
Personally, I like the Instead of Trigger, unless you are willing to alter the schema to a new setup. The reason is this. There's no reason to through any error. If the user adds a duplicate, who cares? There's still a stock one they can use, so just ignore the insert and proceed. If this gets embedded into a high level process that works in stages, the next part of this is to use the tag in some other table as part of an insert. Just have the process end up with the stock tag.
August 22, 2018 at 10:50 am
Steve Jones - SSC Editor - Wednesday, August 22, 2018 10:07 AMPersonally, I like the Instead of Trigger, unless you are willing to alter the schema to a new setup. The reason is this. There's no reason to through any error. If the user adds a duplicate, who cares? There's still a stock one they can use, so just ignore the insert and proceed. If this gets embedded into a high level process that works in stages, the next part of this is to use the tag in some other table as part of an insert. Just have the process end up with the stock tag.
I'veaactually realised since that my update trigger, for the INSTEAD of won't work, due to the lack of a a unique identifier. The INSTEAD OF INSERT is fine, but it'll actually be impossible to create an INSTEAD OF UPDATE in the example schema.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2018 at 10:56 am
A standard "AFTER" trigger will work. Keep in mind that the trigger does not have to be "all or nothing": it can let good INSERTs/UPDATEs apply and only reverse the bad ones.
And, yes, the trigger must be written for best efficiency, but that's easy enough: everyone here will be very glad to help you end up with a very efficient trigger 🙂.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2018 at 11:55 am
Jonathan AC Roberts - Wednesday, August 22, 2018 9:55 AMI'm not sure why you wouldn't want to do this with a check constrint. It's quite easy:
INSERT INTO StockTag(TagName) VALUES ('werwer')
INSERT INTO StockTag(TagName) VALUES ('werwer2')
INSERT INTO CustomTag(UserCode, TagName) VALUES (2, 'azxcv')
INSERT INTO CustomTag(UserCode, TagName) VALUES (3, 'werwer') -- Raises error
-- Now let's violate our desired constraint....
insert into StockTag(TagName) Values('asdf') -- Oops
SELECT * FROM StockTag
SELECT * FROM CustomTag
August 22, 2018 at 12:02 pm
ScottPletcher - Wednesday, August 22, 2018 9:38 AMSo how does that constraint prevent a custom tag from being the same as a standard tag?
The view contains all user tags that also have a matching standard tag, so far so good. The Cross Join with the IndexedViewDoubler table (which has two rows) ensures that every entry that would appear in the view will appear twice. Finally the Unique Constraint on the view prevents any user/tag combination from appearing twice.
The net result of all that is that any user tag which matches a standard tag would make two rows appear in the view, but the unique constraint prevents that and thus any violation is blocked. In effect the view always has to be empty (yes, I should have renamed it - my bad!), because if it weren't the constraint must be being violated. This technique can be applied to pretty much any condition that can be specified by JOINS.
August 22, 2018 at 12:10 pm
Steve Jones - SSC Editor - Wednesday, August 22, 2018 10:07 AMPersonally, I like the Instead of Trigger, unless you are willing to alter the schema to a new setup. The reason is this. There's no reason to through any error. If the user adds a duplicate, who cares? There's still a stock one they can use, so just ignore the insert and proceed. If this gets embedded into a high level process that works in stages, the next part of this is to use the tag in some other table as part of an insert. Just have the process end up with the stock tag.
It depends on the scenario and how the tags are being used elsewhere. If there are "special" tags that only the system should be able to apply - say "NoCharge", "PriorityDelivery" etc - you don't necessarily want users to be able to create matching tags and effectively game the system to gain benefits they shouldn't. Now in that specific situation it'd probably be better overall to completely isolate the two concepts, but there may similar situations in which you don't want an individuals tag to accidentally trigger a processing side-effect.
August 22, 2018 at 12:26 pm
andycadley - Wednesday, August 22, 2018 12:02 PMScottPletcher - Wednesday, August 22, 2018 9:38 AMSo how does that constraint prevent a custom tag from being the same as a standard tag?The view contains all user tags that also have a matching standard tag, so far so good. The Cross Join with the IndexedViewDoubler table (which has two rows) ensures that every entry that would appear in the view will appear twice. Finally the Unique Constraint on the view prevents any user/tag combination from appearing twice.
The net result of all that is that any user tag which matches a standard tag would make two rows appear in the view, but the unique constraint prevents that and thus any violation is blocked. In effect the view always has to be empty (yes, I should have renamed it - my bad!), because if it weren't the constraint must be being violated. This technique can be applied to pretty much any condition that can be specified by JOINS.
You don't need an actual helper "table", you could put two values in-line to cross join to.
Doesn't this have to be processed every time a row gets added or dropped from the table? Seems like a lot more overhead, and a lot more difficult/obscurity, than a simple trigger or CHECK constraint.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2018 at 12:42 pm
ScottPletcher - Wednesday, August 22, 2018 12:26 PMYou don't need an actual helper "table", you could put two values in-line to cross join to.
Doesn't this have to be processed every time a row gets added or dropped from the table? Seems like a lot more overhead, and a lot more difficult/obscurity, than a simple trigger or CHECK constraint.
Unfortunately you do, you can't used derived tables, unions or cross apply in an indexed view so it needs a real table to work (though you can re-use that as you like). Assuming the columns in question are indexed properly, the overhead is reasonably minimal in most cases and has the advantage you haven't got to have logic in multiple places (you need two check constraints in this case, but would need to add one per table that is involved). Well named Views/Constraints can make this less difficult/obscure than triggers to implement in my experience.
You do have to live with the limitations of schema binding though, which may be a reason to prefer multiple checks/triggers. YMMV as usual.
August 22, 2018 at 1:28 pm
andycadley - Wednesday, August 22, 2018 11:55 AMJonathan AC Roberts - Wednesday, August 22, 2018 9:55 AMI'm not sure why you wouldn't want to do this with a check constrint. It's quite easy:Try replacing your INSERTs with:
INSERT INTO CustomTag(UserCode, TagName) VALUES (1, 'asdf')
INSERT INTO StockTag(TagName) VALUES ('werwer')
INSERT INTO StockTag(TagName) VALUES ('werwer2')
INSERT INTO CustomTag(UserCode, TagName) VALUES (2, 'azxcv')
INSERT INTO CustomTag(UserCode, TagName) VALUES (3, 'werwer') -- Raises error
-- Now let's violate our desired constraint....
insert into StockTag(TagName) Values('asdf') -- Oops
SELECT * FROM StockTag
SELECT * FROM CustomTag
That can be fixed with a constraint on StockTagDROP TABLE CustomTag
GO
DROP TABLE StockTag
GO
DROP FUNCTION dbo.CheckNotOnStockTag
GO
CREATE TABLE StockTag
( TagName VARCHAR(50) PRIMARY KEY );
--Here is the constraint I would like to enforce:
GO
CREATE TABLE CustomTag
( UserCode INT NOT NULL
,TagName VARCHAR(50) NOT NULL
,PRIMARY KEY ( UserCode, TagName )
);
GO
CREATE FUNCTION dbo.CheckNotOnStockTag(@TagName varchar(50))
RETURNS int AS
BEGIN
RETURN (SELECT COUNT(*) FROM StockTag WHERE TagName=@TagName)
END
GO
CREATE FUNCTION dbo.CheckNotOnCustomTag(@TagName varchar(50))
RETURNS int AS
BEGIN
RETURN (SELECT COUNT(*) FROM CustomTag WHERE TagName=@TagName)
END
GO
ALTER TABLE CustomTag
ADD CONSTRAINT chk_NotOnStockTag
CHECK (dbo.CheckNotOnStockTag(TagName)=0)
GO
ALTER TABLE StockTag
ADD CONSTRAINT chk_NotOnCustomTag
CHECK (dbo.CheckNotOnCustomTag(TagName)=0)
GO
INSERT INTO StockTag(TagName) VALUES ('werwer')
INSERT INTO StockTag(TagName) VALUES ('werwer2')
INSERT INTO CustomTag(UserCode, TagName) VALUES (1, 'asdf')
INSERT INTO CustomTag(UserCode, TagName) VALUES (2, 'azxcv')
--INSERT INTO CustomTag(UserCode, TagName) VALUES (3, 'werwer') -- Raises error
INSERT INTO StockTag(TagName) VALUES ('asdf')
SELECT * FROM StockTag
SELECT * FROM CustomTag
Out of all the solutions I prefer sgmunson's idea of changing the table design so they are all in one table.
August 22, 2018 at 7:06 pm
You can do this using triggers. As I mentioned in an earlier reply, I prefer to avoid triggers where possible for a number of reasons. (See the previous reply.)
Similar arguments apply to using a Check constraint which references a function. I really like Andy Cadley's solution here. I am split about 50-50 between using that and reworking the table design. However, i can imagine scenarios where Andy's technique would be very useful for enforcing a No Intersection rule.
As I stated earlier, I prefer, where possible, to solve problems via table design rather than code. It can take more effort up front, but you end up with a cleaner design and code which is easier to maintain.
Good discussion, everyone! 😀
August 23, 2018 at 7:47 am
David Moutray - Wednesday, August 22, 2018 7:06 PMYou can do this using triggers. As I mentioned in an earlier reply, I prefer to avoid triggers where possible for a number of reasons. (See the previous reply.)Similar arguments apply to using a Check constraint which references a function. I really like Andy Cadley's solution here. I am split about 50-50 between using that and reworking the table design. However, i can imagine scenarios where Andy's technique would be very useful for enforcing a No Intersection rule.
As I stated earlier, I prefer, where possible, to solve problems via table design rather than code. It can take more effort up front, but you end up with a cleaner design and code which is easier to maintain.
Good discussion, everyone! 😀
That's true. I prefer a trigger because you can allow "good" rows through and store any "bad" rows for later re-processing. A constraint is a forced fail, period.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply