May 9, 2018 at 10:04 pm
I am new on writing and testing trigger. I am trying to write a trigger to monitor ta colume wih the table. Wether it is insert or update, if that column has a period there, the trigger will remove the period on that column. will this trigger recursive? also how can i get out the deadlock/infinite loop when i test this trigger. Thanks for any help....
CREATE TRIGGER ON Table1
AFTER insert, update
AS
BEGIN
set nocount on
UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue))
FROM Mytable t
inner join inserted i on i.Myvalue like '%.%'
END
May 10, 2018 at 12:19 am
I don't think you wanna do what you wrote in the trigger. You should find a unique column or a set of columns in your table to use in the JOIN's ON statement.
The like '%.%' belongs to a WHERE clause.
The other failures are typos i think:
- trigger has no name
- trigger on table Table1 updates Mytable without any reference to Table1
May 10, 2018 at 3:33 am
I'm not really sure what you mean by "Will it Recursive?". Do you mean, will it end up self triggering? If so, no; the trigger is on TABLE1 and it is performing an UPDATE statement on MyTable; a completely different object.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2018 at 5:28 am
patrick-330430 - Wednesday, May 9, 2018 10:04 PMI am new on writing and testing trigger. I am trying to write a trigger to monitor ta colume wih the table. Wether it is insert or update, if that column has a period there, the trigger will remove the period on that column. will this trigger recursive? also how can i get out the deadlock/infinite loop when i test this trigger. Thanks for any help....CREATE TRIGGER ON Table1
AFTER insert, update
AS
BEGIN
set nocount on
UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue))
FROM Mytable t
inner join inserted i on i.Myvalue like '%.%'
END
SQL Server triggers will work against all of the rows inserted by a given transaction. I agree with the others, though. You should join the INSERTED table to the table in question based on the primary key of the table in question. The Join you currently have will form a Caresian Product, which will look like an infinite loop and likely cause deadlocks, as well..
Also, your trigger is an AFTER trigger. That means that your UPDATE is double-dipping the table. After the INSERT has occurred, you're going back to those rows, reading all of them again, and then UPDATING them. If you use an INSTEAD OF trigger to do this, your modification will occur as a part of the original INSERT.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2018 at 5:30 am
thanks palotaiarpad & Thom A. Yes, I was asking if this end up self triggering. I correct the code as follow:
CREATE TRIGGER Mytrigger ON Mytable
AFTER insert, update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
END
May 10, 2018 at 7:58 am
Thanks Jeff Moden. Are you saying the following change will avoid self-triggering and double dipping?
CREATE TRIGGER Mytrigger ON Mytable
INSTEAD OF insert, update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
END
Also, this may be a stupid question....when i test the trigger, how can i get out of the infinite loop and possible show some logs to confirm the trigger does happen?
May 10, 2018 at 8:08 am
patrick-330430 - Thursday, May 10, 2018 7:58 AMThanks Jeff Moden. Are you saying the following change will avoid self-triggering and double dipping?CREATE TRIGGER Mytrigger ON Mytable
INSTEAD OF insert, update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
ENDAlso, this may be a stupid question....when i test the trigger, how can i get out of the infinite loop and possible show some logs to confirm the trigger does happen?
I'd suggest making a separate triggers for the INSERT and UPDATE. Otherwise your inserts are never going insert any data (as the id won't exist in MyTable yet).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2018 at 9:50 am
Also, don't you want to subtract 1 from the substring length to you get rid of the period and everything after it in the string (that seems to me to be what you're trying to do).
UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue) - 1)
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".
May 10, 2018 at 10:10 am
ScottPletcher - Thursday, May 10, 2018 9:50 AMAlso, don't you want to subtract 1 from the substring length to you get rid of the period and everything after it in the string (that seems to me to be what you're trying to do).UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue) - 1)
i did that without -1.
For example, 402.123 become 402
May 10, 2018 at 10:18 am
Thom A - Thursday, May 10, 2018 8:08 AMpatrick-330430 - Thursday, May 10, 2018 7:58 AMThanks Jeff Moden. Are you saying the following change will avoid self-triggering and double dipping?CREATE TRIGGER Mytrigger ON Mytable
INSTEAD OF insert, update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
ENDAlso, this may be a stupid question....when i test the trigger, how can i get out of the infinite loop and possible show some logs to confirm the trigger does happen?
I'd suggest making a separate triggers for the INSERT and UPDATE. Otherwise your inserts are never going insert any data (as the id won't exist in MyTable yet).
Thanks Thom.... if I separate Insert and update, should I use AFTER for insert and INSTEAD OF for update? how does this look?
CREATE TRIGGER Mytrigger1 ON Mytable
INSTEAD OF update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
END
CREATE TRIGGER Mytrigger2 ON Mytable
AFTER insert
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
END
May 10, 2018 at 10:24 am
patrick-330430 - Thursday, May 10, 2018 10:10 AMScottPletcher - Thursday, May 10, 2018 9:50 AMAlso, don't you want to subtract 1 from the substring length to you get rid of the period and everything after it in the string (that seems to me to be what you're trying to do).UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue) - 1)
i did that without -1.
For example, 402.123 become 402
I guess it does work, by starting at byte 0. I'm not a fan of those types of hidden "tricks" (such as using -1 for a date instead of just directly subtracting a day). Hopefully it keeps working correctly for you.
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".
May 11, 2018 at 6:23 pm
Jeff Moden - Thursday, May 10, 2018 5:28 AMpatrick-330430 - Wednesday, May 9, 2018 10:04 PMI am new on writing and testing trigger. I am trying to write a trigger to monitor ta colume wih the table. Wether it is insert or update, if that column has a period there, the trigger will remove the period on that column. will this trigger recursive? also how can i get out the deadlock/infinite loop when i test this trigger. Thanks for any help....CREATE TRIGGER ON Table1
AFTER insert, update
AS
BEGIN
set nocount on
UPDATE t set t.Myvalue=substring(t.Myvalue,0,charindex('.',t.Myvalue))
FROM Mytable t
inner join inserted i on i.Myvalue like '%.%'
ENDSQL Server triggers will work against all of the rows inserted by a given transaction. I agree with the others, though. You should join the INSERTED table to the table in question based on the primary key of the table in question. The Join you currently have will form a Caresian Product, which will look like an infinite loop and likely cause deadlocks, as well..
Also, your trigger is an AFTER trigger. That means that your UPDATE is double-dipping the table. After the INSERT has occurred, you're going back to those rows, reading all of them again, and then UPDATING them. If you use an INSTEAD OF trigger to do this, your modification will occur as a part of the original INSERT.
I will just add that there can only be one instead of trigger for a particular operation, like an insert, for a given table.
----------------------------------------------------
May 11, 2018 at 6:40 pm
Thom A - Thursday, May 10, 2018 8:08 AMpatrick-330430 - Thursday, May 10, 2018 7:58 AMThanks Jeff Moden. Are you saying the following change will avoid self-triggering and double dipping?CREATE TRIGGER Mytrigger ON Mytable
INSTEAD OF insert, update
AS
BEGIN
set nocount on
UPDATE Mytable set Myvalue=substring(Myvalue,0,charindex('.',Myvalue))
FROM Mytable t
inner join inserted i on t.id=i.id and exists(i.Myvalue like '%.%')
ENDAlso, this may be a stupid question....when i test the trigger, how can i get out of the infinite loop and possible show some logs to confirm the trigger does happen?
I'd suggest making a separate triggers for the INSERT and UPDATE. Otherwise your inserts are never going insert any data (as the id won't exist in MyTable yet).
Just to note , you can use an instead of trigger on a insert to insert the values as they are still in the INSERTED hidden table.
/* start a test table */
create table mmtest (id int, val varchar(10))
/* add some values before the trigger */
insert into mmtest
values
(1,'val1'),
(2,'val.2'),
(3,'val.3'),
(4,'Val4')
/* create the trigger */
create trigger mmTempTrg on mmtest
instead of insert
as
begin
set nocount on;
insert into mmtest
select i.id, replace(i.val,'.','')
from inserted as i
end
/* add a row with the trigger now in place */
insert into mmtest
select 5,'val.5'
/* we can see the period was not part of the insert of the string */
select * from mmtest
--/ *clean up */ drop table mmtest
Here you dont need to join on ID of course because as prior post mentioned, the ID is not in the table yet. I just wanted to illustrate how to implement the instead of trigger on a insert.
----------------------------------------------------
May 12, 2018 at 2:08 pm
Thanks MMartin1. That works perfectly. Is it possible to have a similar one for Update? I tried to add the Update but it will do the insert whenever update happen.
May 12, 2018 at 7:53 pm
patrick-330430 - Saturday, May 12, 2018 2:08 PMThanks MMartin1. That works perfectly. Is it possible to have a similar one for Update? I tried to add the Update but it will do the insert whenever update happen.
Sure, I created a separate trigger for updates . Here I continued with using the instead of variety ...
Lets assume you have not dropped the example table yet ,
/* continuing on with the example ..
update a value with one that has a period in the string */
update mmtest
set val = 'val.1'
where id = 1
/*check */ select * from mmtest
/* Now create the the trigger to prevent this type of update from happening again */
create trigger mmTempTrg_upd on mmtest
instead of update
as
begin
set nocount on;
update t
set t.val = replace(t.val,'.','')
from mmtest as t
inner join inserted as i
on i.id = t.id
end
/* update a row with the UPDATE trigger now in place */
update mmtest
set val = 'val.4'
where id = 4
/* check again */ select * from mmtest
/* notice we prevented the period from being part of the string */
--/ *clean up */ drop table mmtest
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply