AFTER INSERT Trigger Not Firing In SQL 2005
Jul 28, 2006
Nothing fancy; just a trigger on a sharepoint table that supposed to
write a record to another SQL table.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [TV_UpdateFileSyncProgress]
ON [dbo].[Docs]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN
IF EXISTS (SELECT null FROM inserted WHERE DirName like
'csm/%/Shared Documents')
BEGIN
IF NOT EXISTS (SELECT null FROM inserted INNER JOIN
TV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =
LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/Shared
Documents','') + '' + inserted.LeafName)))
BEGIN
INSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,
ChangeType, SiteId, DirName, LeafName, FlagForDelete)
SELECT
0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/Shared
Documents','') + '' + LeafName,0 FROM inserted
END
END
END
END
View 1 Replies
ADVERTISEMENT
Nov 29, 2006
hi all
i have an issue with an insert trigger sometimes not firing.
here is the trigger
CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS
INSERT into tPABillToAddr
(
chrJobNumber
)
SELECT chrJobNumber
FROM inserted
when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.
thanks
View 4 Replies
View Related
Oct 6, 2007
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
View 1 Replies
View Related
Aug 27, 2004
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?
View 6 Replies
View Related
Jun 7, 1999
Hi,
Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....
Davy
View 1 Replies
View Related
Dec 7, 2007
OK, I'm at a loss..it must be staring me right in the face.
I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.
Any ideas?
CREATE TABLE [dbo].[PIF_MEP99] (
[PIFRecID] [int] NOT NULL ,
[MEPRecID] [int] NOT NULL ,
[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD
CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED
(
[PIFRecID],
[MEPRecID]
) ON [PRIMARY]
GO
INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO
CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99
FOR UPDATE, DELETE
AS
SET NOCOUNT ON
-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary
IF Exists ( SELECT * FROM inserted i
INNER JOIN PIF_MEP99 p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y')
BEGIN
ROLLBACK TRAN
RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP'
END
GO
SELECT * FROM PIF_MEP99
GO
SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO
BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO
SELECT * FROM PIF_MEP99
GO
SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO
DROP TABLE PIF_MEP99
GO
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
View 3 Replies
View Related
Mar 27, 2006
I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.
I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.
Any help would be appreciated.
View 2 Replies
View Related
May 23, 2008
Afternoon All ...
I have a trigger on a table that works great when a single record is updated BUT it seems not to fire when I try to do a bulk update as in ...
update TableA
set field3 = ms.field3
FROM TableA as ta,
#ms as ms
where field1 = ms.field1
and field2 = ms.field2
Why does this happen and how can I correct it.
Thanks!
View 1 Replies
View Related
Nov 18, 2005
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...
Is DTS capable of updating a SQL Server table without firing the trigger?
I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.
FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26)
Carl
CREATE TRIGGER trig_SAWakeUp ON tsnro
FOR INSERT, UPDATE
AS
DECLARE @snro_stus char(10)
DECLARE @snp_sht_dtm as datetime
SELECT @snro_stus = snro_stus
FROM tsnro
IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED')
BEGIN
SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19))
FROM tsnro
INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END
ELSE
BEGIN
INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END
View 2 Replies
View Related
Jan 13, 2004
Hi,
I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.
Any Ideas..
View 5 Replies
View Related
Jun 18, 2014
1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2 fields(chat_id ,country), chat table will store all countries data (India,china,taiwan)
2) chat_id,language will be populated by insert statement, and chat_info by update statement, i have a below after update trigger on chat table,which should verify country from media table and copy respective record from chat table to chat_country table (chat_in,chat_cn,chat_tw - these tables will have same structure as chat table)
3) this trigger is not firing for some of the records ,no clue or info for which it is missing some records.
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat]
after update
as
BEGIN
[code]...
View 3 Replies
View Related
Feb 3, 2006
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ
View 1 Replies
View Related
Nov 22, 2007
Hi All,
I have an update trigger which stores the date a record was updated by a GIS app.
It worked in SQL2000 but when I copied and pasted the code into SQL2 its not firing. Any Ideas? Below is the code.
Create TRIGGER [dbo].[Date_Entered] ON [dbo].[ZONING_OP]
FOR UPDATE
AS
UPDATE [dbo].[ZONING_OP]
Set Date_Entered = GetDate()
Where ID = (Select ID from Inserted)
View 6 Replies
View Related
Apr 11, 2001
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE --DT.MODIFIED_BY <> 'DBO' AND
PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end
View 3 Replies
View Related
May 10, 2006
Hi,
Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:
sql Code:
Original
- sql Code
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync
ON smvcModImageManagerCategory
FOR INSERT, DELETE
AS
DECLARE @pdfId INTEGER;
DECLARE @parentPdfId INTEGER;
DECLARE @grandparentPdfId INTEGER;
DECLARE @parentId INTEGER;
DECLARE @grandparentId INTEGER;
DECLARE @tableName VARCHAR( 255 );
-- If I am being inserted or deleted, and I am not a top level
-- category, then my parent's pdf record needs to be set so that
-- the pdf file is updated
IF (SELECT id FROM Inserted) IS NOT NULL
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Inserted),
@parentId = (SELECT parentCategory FROM Inserted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId);
END
ELSE
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Deleted),
@parentId = (SELECT parentCategory FROM Deleted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId)
END
-- If I am not a top level category, set my parent's pdf to be
-- updated
IF @parentId <> -1
BEGIN
SELECT @parentPdfId = (SELECT pdfManagerId
FROM smvcModImageManagerCategory
WHERE id = @parentId);
UPDATE smvcModPdfManager SET data_last_updated = GETDATE()
WHERE id = @parentPdfId;
END
GO
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO
However, when I execute a statement like:
sql Code:
Original
- sql Code
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.
Thank you.
View 1 Replies
View Related
Feb 5, 2004
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.
Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.
USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tr_update_users' AND type = 'TR')
DROP TRIGGER tr_update_users
GO
CREATE TRIGGER tr_update_users
on dbo.Users
FOR UPDATE
AS
DECLARE @m_status_new varchar(100),
@m_status_old varchar(100),
@m_UserID int
SELECT @m_status_new = UserStatus,
@m_UserID = UserID
FROMinserted
SELECT @m_status_old = UserStatus
FROMUsers
WHEREUserID = @m_UserID
IF @m_status_new != @m_status_old
BEGIN
print 'status has changed'
END
ELSE
BEGIN
print 'status has not changed'
END
GO
View 6 Replies
View Related
Jul 20, 2005
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.
View 1 Replies
View Related
Jun 11, 2008
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.
CREATE TRIGGER tr_new_file ON FTPLog
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @filename varchar(50), @logtime datetime
DECLARE c1 CURSOR
FOR SELECT filename, logtime
FROM inserted
OPEN c1
FETCH NEXT FROM c1
INTO @filename, @logtime
WHILE @@fetch_status = 0
BEGIN
EXEC sp1 @filename, @logtime
FETCH NEXT FROM c_inserted
INTO @filename, @logtime
END
CLOSE c1
DEALLOCATE c1
END
PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.
PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.
SOLUTIONS and SUGGESTIONS highly appreciated.
View 13 Replies
View Related
Apr 23, 2015
we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also
View 7 Replies
View Related
Feb 23, 2008
I have three tables:
BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records
BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table
Member
This issue:
> When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should
> If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire
Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this?
Any suggestions or comments would be great.
Thanks,
Mike
View 8 Replies
View Related
Jun 7, 2007
Is it possible to create a trigger in one database, that after aninsert, will update a database on a different server?If so, how would I do this?Thanks.Bill
View 1 Replies
View Related
Apr 23, 2008
I have a stored proc that inserts records into a table, gets the identity, and uses that identity on a .net webpage. The page retrieves that identity key and processes some stuff on the page.
Everything was working fine until I tried to enable sql mail with triggers. I wanted to double-check some stuff, so I requested an sql email be sent whenever an insert occurs on the table above. Here's what I think is happening --- please correct and/or help me out with a work around?
I use the stored proc to insert a record.
I select the @@identity.
The trigger fires but uses select to retrieve the latest insert - thereby replacing the @@identity number returned to the page?
If this is true, could I do something like the following in the original stored proc? Is this a good idea or bad idea?
BEGIN
Declare @myID as int, @myBody1 as varChar(200)
Set @myID=0
INSERT INTO table (fields) VALUES (@PID, more stuff);
Set @myID = SELECT @@IDENTITY As [Identity];
If @myID<>0
Begin
Set @body1='<br />pid=' + more stuff.....
Exec msdb.dbo.sp_send_dbmail
@profile_name='profileName',
@recipients='email@email.com',
@subject='Temp History Insert',
@body=@body1,
@body_format= 'HTML' ;
End
END
View 3 Replies
View Related
Mar 22, 2006
Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/
View 2 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Feb 9, 2007
Setting: sql 2005, SP1.
I created a new job (under SQL Server agent) which runs a simple ActiveX script. When run interactively, the job runs fine. But when run under the scheduler the job fails to even start. There is no error message and "History" makes no reference at all to the job run. I checked, the job is enabled. Any thoughts much appreciated.
TIA,
barkingdog
View 1 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Nov 25, 2006
In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,
View 1 Replies
View Related
Nov 20, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
Dec 3, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
