September 7, 2010 at 2:43 pm
Hi!
im triying to do the following:
1.-Create an after insert trigger in table 'A'.
2.-That trigger must change the values of col1 and col2, when they meet the conditions and then insert them as new records in table 'A' with the new values for col1 and col2.
3.-The conditions are;
col1 = 'n'.
col2 = 'm'.
Thanks in advance.
September 7, 2010 at 3:01 pm
the core of the trigger is going to be something like this, but you didn't provide all the info needed. what values would col1 and col2 actually be assigned?
UPDATE A
SET col1 = newvalue,col2 = newvalue2
FROM INSERTED
WHERE A.ID = INSERTED.ID
AND col1 = 'n'
and col2 = 'n'
Lowell
September 7, 2010 at 3:09 pm
I think you're looking for something like:
CREATE TRIGGER tr_test ON dbo.A FOR INSERT AS
UPDATE inserted
SET col1 = <value>
where col1 = 'm'
UPDATE inserted
SET col2 = <value2>
where col2 = 'n'
END
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 3:43 pm
Craig Farrell (9/7/2010)
I think you're looking for something like:CREATE TRIGGER tr_test ON dbo.A FOR INSERT AS
UPDATE inserted
SET col1 = <value>
where col1 = 'm'
UPDATE inserted
SET col2 = <value2>
where col2 = 'n'
END
can you update the virtual INSERTED or DELETED tables? i don't think that would have any effect, would it?
Msg 286, Level 16, State 1, Procedure TR_WHATEVER, Line 5
The logical tables INSERTED and DELETED cannot be updated.
Lowell
September 7, 2010 at 3:53 pm
Msg 286, Level 16, State 1, Procedure TR_WHATEVER, Line 5
The logical tables INSERTED and DELETED cannot be updated.
Um, errr... dang, I'd have sworn I've done that before. I'm off to research and test, but hopefully someone comes back with a better answer in the meanwhile. (Sorry, please ignore the man behind the curtain).
At least it's an example of what the main poster wanted, if he comes back and says yes. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 4:19 pm
Lowell (9/7/2010)
the core of the trigger is going to be something like this, but you didn't provide all the info needed. what values would col1 and col2 actually be assigned?
UPDATE A
SET col1 = newvalue,col2 = newvalue2
FROM INSERTED
WHERE A.ID = INSERTED.ID
AND col1 = 'n'
and col2 = 'n'
thanks!!
im using this one because the other one cant get updated.
Ive a question if you excuse my 'newbieness', what does 'id' means.
September 7, 2010 at 4:23 pm
igngua (9/7/2010)
thanks!!
im using this one because the other one cant get updated.
Ive a question if you excuse my 'newbieness', what does 'id' means.
i had to infer a lot of missing information...
in order to update one table from another, there has to be a relationship between the two;
i assumed that the table had some kind of an identity/primary key named "ID" you know, in your CREATE TABLE statement.
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY....)
so in order to update the table from the vurtual table INSERTED, you would have to change "ID" to whatever the real key (or keys) are that identify a single row as unique.
Lowell
September 7, 2010 at 4:32 pm
Lowell (9/7/2010)
igngua (9/7/2010)
thanks!!
im using this one because the other one cant get updated.
Ive a question if you excuse my 'newbieness', what does 'id' means.
i had to infer a lot of missing information...
in order to update one table from another, there has to be a relationship between the two;
i assumed that the table had some kind of an identity/primary key named "ID" you know, in your CREATE TABLE statement.
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY....)
so in order to update the table from the vurtual table INSERTED, you would have to change "ID" to whatever the real key (or keys) are that identify a single row as unique.
does this help.??
CREATE TABLE [dbo].[EICANALESRES](
[IdContrato] [decimal](10, 0) NOT NULL,
[EiCanalId] [decimal](10, 0) NOT NULL,
[EiFechServ] [datetime] NOT NULL,
[EiFechMaq] [datetime] NOT NULL,
[EiFechCorr] [datetime] NOT NULL,
[EiValorFis] [decimal](25, 4) NOT NULL,
[EiValorNum] [decimal](25, 4) NOT NULL,
[EiValorCalc] [decimal](25, 4) NOT NULL,
[EiEst] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[IdContrato] ASC,
[EiCanalId] ASC,
[EiFechServ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
September 7, 2010 at 4:41 pm
yep, so your WHERE statement would reference the THREE columns that make up your primary key:
...WHERE TableName.IdContrato = INSERTED.IdContrato
AND TableName.EiCanalId = INSERTED.EiCanalId
AND TableNme.EiFechServ = INSERTED.EiFechServ
AND Cola = 'n'
AND colb = 'm'
Lowell
September 7, 2010 at 4:41 pm
igngua (9/7/2010)
.[EICANALESRES]([IdContrato] [decimal](10, 0) NOT NULL,
[EiCanalId] [decimal](10, 0) NOT NULL,
[EiFechServ] [datetime] NOT NULL,
[EiFechMaq] [datetime] NOT NULL,
[EiFechCorr] [datetime] NOT NULL,
[EiValorFis] [decimal](25, 4) NOT NULL,
[EiValorNum] [decimal](25, 4) NOT NULL,
[EiValorCalc] [decimal](25, 4) NOT NULL,
[EiEst] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[IdContrato] ASC,
[EiCanalId] ASC,
[EiFechServ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Figured out what i did wrong, it had been a while, and you're right about not directly affecting inserted Lowell, but using an instead of trigger will help keep the # of passes down (Use the case statement around EiFechMaq as you need it, I'm assuming it's col1)
CREATE TRIGGER TR_UpdTestTrig ON EICANALESRES
INSTEAD OF UPDATE
AS
BEGIN
UPDATEcr
SET
[EiFechMaq] = CASE i.EiFechMaq WHEN 'm' THEN 'OtherValue' ELSE i.EiFechMaq END,
[EiFechCorr] = i.EiFechCorr,
[EiValorFis] = i.EiValorFis,
[EiValorNum] = i.EiValorNum,
[EiValorCalc] = i.EiValorCalc,
[EiEst] = i.EiEst
FROMEICANALESRES AS cr
JOIN
inserted as i
ONcr.IdContrato = i.IdContrato
AND cr.EiCanalId = i.EiCanalID
AND cr.EiFechServ = i.EiFechServ
END
GO
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 5:33 pm
Thanks lowell.
Ive tried it on a hurry while leaving work. Ill post back if something goes wrong.
September 8, 2010 at 10:24 am
Ok...after triying and triying this is closer to an answer but it still goes wrong.
ALTER TRIGGER [dbo].[datosdemo]
ON [dbo].[EICANALESRES]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
update dbo.eicanalesres
SET idcontrato = '13'
FROM INSERTED
WHERE inserted.idcontrato = '02'
AND inserted.EiCanalId = '91'
and inserted.eifechserv = eicanalesres.eifechserv
end
What this does is:
- When i insert records on dbo.eicanalesres where id contrato is = 2 and eicanal is = 91, it writes those records in the same table, updating idcontrato to =13.
-But i get ther following error in the insert;
"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."
- When i run the query again for the original insert the records disappear.
- the update occurs and the records remains.
thanks in advance.
September 9, 2010 at 7:13 am
any ideas??
September 9, 2010 at 7:18 am
update dbo.eicanalesres
SET idcontrato = '13'
FROM INSERTED
WHERE dbo.eicanalesres.IdContrato = INSERTED.IdContrato
AND dbo.eicanalesres.EiCanalId = INSERTED.EiCanalId
AND dbo.eicanalesres.EiFechServ = INSERTED.EiFechServ
AND inserted.idcontrato = '02'
AND inserted.EiCanalId = '91'
and inserted.eifechserv = eicanalesres.eifechserv
you missed the core of the trigger: to update the base table from inserted, you MUST JOIN the two on the primary key...you identified that as three specific columns.
Lowell
September 9, 2010 at 7:34 am
Lowell (9/9/2010)
update dbo.eicanalesres
SET idcontrato = '13'
FROM INSERTED
WHERE dbo.eicanalesres.IdContrato = INSERTED.IdContrato
AND dbo.eicanalesres.EiCanalId = INSERTED.EiCanalId
AND dbo.eicanalesres.EiFechServ = INSERTED.EiFechServ
AND inserted.idcontrato = '02'
AND inserted.EiCanalId = '91'
and inserted.eifechserv = eicanalesres.eifechserv
you missed the core of the trigger: to update the base table from inserted, you MUST JOIN the two on the primary key...you identified that as three specific columns.
USE [rtbweb]
GO
/****** Object: Trigger [dbo].[datosdemo] Script Date: 09/09/2010 09:13:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[datosdemo]
ON [dbo].[EICANALESRES]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
update dbo.eicanalesres
SET idcontrato = '13'
FROM INSERTED
WHERE eicanalesres.idcontrato = inserted.idcontrato
AND eicanalesres.eicanalid = inserted.eicanalid
and eicanalesres.eifechserv = inserted.eifechserv
and inserted.idcontrato = '02'
and inserted.eicanalid = '91'
and inserted.eifechserv = eicanalesres.eifechserv
end
i got the same error...:(
"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply