February 11, 2016 at 11:16 am
If a record does not exist in this table the update on "SomeTable" should work. When I debug I see it working the way I would expect but the update is not committing. I am thinking the issue is "Instead of" or I am screwing something up. Please help
ALTER TRIGGER [dbo].[BadUpdate]
ON [dbo].[SomeTable]
INSTEAD OF UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM dbo.Foo
WHERE Field IN (
SELECT inserted.Field
FROM inserted
WHERE inserted.IsActive = 0))
BEGIN
RAISERROR ('Illegal move game master' ,16,1)
ROLLBACK TRANSACTION
END
END
GO
February 11, 2016 at 11:35 am
JKSQL (2/11/2016)
If a record does not exist in this table the update on "SomeTable" should work. When I debug I see it working the way I would expect but the update is not committing. I am thinking the issue is "Instead of" or I am screwing something up. Please help
ALTER TRIGGER [dbo].[BadUpdate]
ON [dbo].[SomeTable]
INSTEAD OF UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM dbo.Foo
WHERE Field IN (
SELECT inserted.Field
FROM inserted
WHERE inserted.IsActive = 0))
BEGIN
RAISERROR ('Illegal move game master' ,16,1)
ROLLBACK TRANSACTION
END
END
GO
Which update? Your trigger needs to code the update itself, presumably as part of an ELSE block in your code.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 11, 2016 at 11:38 am
Yes, you are trying to use a trigger when you should be doing this test prior to doing the insert.
IF NOT EXISTS(SELECT 1 FROM SomeTable WHERE whatever test you use) Begin
--Do your insert
End
This is certainly not a good use for a trigger.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2016 at 1:08 pm
OK so it is not a pass/fail. It is a Pass -> UPDATE SomeTable SET WHERE. That is what I was kind of thinking but wasn't sure. Thanks
February 11, 2016 at 1:47 pm
JKSQL (2/11/2016)
OK so it is not a pass/fail. It is a Pass -> UPDATE SomeTable SET WHERE. That is what I was kind of thinking but wasn't sure. Thanks
If you change the trigger from "INSTEAD OF UPDATE" to "FOR UPDATE" the updates will be committed if no error raised in the trigger.
_____________
Code for TallyGenerator
February 11, 2016 at 1:55 pm
You need to test for the existence of a record in a table, and if it does not exist, you want to insert a record in another table, correct?
My question is why do you think that a trigger is the best place to do this?
In your code, do the test, and then do the appropriate insert.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2016 at 2:22 pm
I thought we were saying something like this would work
ALTER TRIGGER [dbo].[BadUpdate]
ON [dbo].[SomeTable]
INSTEAD OF UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1 FROM dbo.Foo
WHERE Field IN (
SELECT inserted.Field
FROM inserted
WHERE inserted.IsActive = 0))
BEGIN
RAISERROR ('Illegal move game master' ,16,1)
ROLLBACK TRANSACTION
END
ELSE
UPDATE dbo.SomeTable
SET
[Name] = inserted.Name
WHERE [Id] = inserted.Id
END
GO
That did not work. the Update does not change the field name
UPDATE dbo.SomeTable
SET
[Name] = inserted.Name
WHERE [Id] = inserted.Id
I have an error saying inserted is not a good alias
February 11, 2016 at 2:28 pm
ALTER TRIGGER [dbo].[BadUpdate]
ON [dbo].[SomeTable]
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF EXISTS (
SELECT 1
FROM dbo.Foo
WHERE Field IN (
SELECT inserted.Field
FROM inserted
WHERE inserted.IsActive = 0 )
) BEGIN
RAISERROR ('Illegal move game master' ,16,1)
ROLLBACK TRANSACTION
END /*IF*/
ELSE BEGIN
UPDATE st
SET non_key_col1 = i.non_key_col1,
non_key_col2 = i.non_key_col2--,...
FROM inserted i
INNER JOIN dbo.SomeTable st ON st.key_col = i.key_col
END /*ELSE*/
GO --end of 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".
February 11, 2016 at 2:31 pm
JKSQL (2/11/2016)
I thought we were saying something like this would work
Change the type of trigger and your initial code will be just fine.
I'd only recommend replacing "IN" with an "INNER JOIN".
_____________
Code for TallyGenerator
February 11, 2016 at 2:48 pm
Thanks guys for getting me resolved quickly. works well now. Triggers always mess with me. I would have thought that first statement would work. It passed the rollback portion so I figured it would commit the record on its own. I am surprised it makes me write an update statement.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply