MS SQL Server :: AFTER INSERT Trigger Not Firing In SQL 2005

Trigger Not Firing
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 Replies !

Trigger Not FIRING With BCP
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 Replies !

Trigger Not Firing ...
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 Replies !

Trigger Not Firing

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 Replies !

Trigger Not Firing On Update
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 Replies !

Trigger Not Firing, DTS Load
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 Replies !

Prevent Trigger From Firing
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 Replies !

Artificially Firing A TRIGGER
I have a table (table 1) that has a trigger attached to it, which sends data to table 2. Both tables are attached to an appliction.

In theory, the trigger populates table 2 (from table 1) from the data entered from the user interface application.

But when I import data from the backend

via DTS into Table 1, the trigger does not send the data to table 2.

What do I need to do ???

Thank you in advance !!

View Replies !

Update Trigger Not Firing.

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 Replies !

Create Trigger Not Firing Via ODBC
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 Replies !

UPDATE Trigger Firing Too Late?
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 Replies !

UPDATE TRIGGER WITHOUT RECURSIVE FIRING
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 Replies !

Error W/ Trigger When Firing On 'DELETE XYZ WHERE IN (1,2,3)'
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 Replies !

Trigger Not Firing On Cascade Delete Table
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 Replies !

Trigger Firing Multiple Stored Procedure
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 Replies !

SQL 2005 : AFTER INSERT - Trigger
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 Replies !

SQL Server 2005: TRIGGER AFTER INSERT
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 Replies !

Sql 2005 Trigger Insert And Dbmail
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 Replies !

Sql 2005 Scheduled Job Not Firing
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 Replies !

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
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 Replies !

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
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 Replies !

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
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 Replies !

Multiple Insert Call For A Table Having Insert Trigger
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 Replies !

Insert Trigger For Bulk Insert
In case of a bulk insert, the â€œFOR INSERTâ€? trigger fires for each recod or only once?
Thanks,

View Replies !

Interaction Between &&"instead Of Insert&&" Trigger And Output Clause Of Insert Statement

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 Replies !

Insert Trigger
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 Replies !

Insert Trigger - How To
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 Replies !

Insert Trigger Help
Hello,I'm new with triggers and I can not find any good example on how todo the following:I have two tables WO and PM with the following fields:WO.WONUM, VARCHAR(10)WO.PMNUM, VARCHAR(10)WO.PROBLEMCODE, VARCHAR(8)WO.LABORGROUP, VARCHAR(8)PM.PMNUM, VARCHAR(10)PM.PROBLEMCODE, VARCHAR(8)PM.LABORGROUP, VARCHAR(8)When creating a new record on WO I need to create an INSERT TRIGGERthat will pass the data below from PM to WO when WO.PMNUM = PM.PMNUMPM.PROBLEMCODE to WO. PROBLEMCODE andPM.LABORGROUP to WO. LABORGROUPCould anybody please show me how to do this or point me to the rightdirection, any help will be greatly appreciated.Thanks!Martin

View Replies !

Insert Trigger
HiI have a table - DebtorTurnover - consisting of 5 fields (ID, Date,Turnover, VAT, Netturnover). I get a file which I have to import everyknow and then, with new data. In this file I only get values for (ID,Date, Turnover and VAT). The import is working fine with the importwizard.The problem is, that I want to have the Netturnover computed at thetime of insert to equal [Turnover-VAT], but I don't really know how toas I'm new to these triggers.Could anyone help me I would appriciate this.BR / Jan

View Replies !

Trigger INSERT Q
Really simple question - I have the following trigger for the INSERT event for Tbl_B:

CREATE TRIGGER calc_total ON [dbo].[Tbl_B]
FOR INSERT
AS

DECLARE @VATRate AS decimal(19,2)
SET @VATRate = (
SELECT Val
FROM Tbl_Numeric_Refs
WHERE Ref = 'VATRate'
)

UPDATE Tbl_B
SET [Total] = ((@VATRate / 100) * [PriceA]) + [PriceA],
[VATRate] = @VATRateHow can I restrict the UPDATE within the trigger to the record(s) being INSERTed? Or am I over-complicating things, and should use a simple UDF for this instead?

View Replies !

Trigger On Insert
Is it possible to pick up the value that is being inserted within the scope of a trigger? For example, if I were to run the following

INSERT INTO people (firstname, lastname)
VALUES ('George', 'V')

Would it be possible to access these values in a trigger before they are inserted?

CREATE TRIGGER trigger1
ON people
FOR INSERT
AS
IF EXISTS (SELECT 1 FROM table1 WHERE firstname = <the value being inserted>) BEGIN
Print '1'
END
ELSE BEGIN
Print '2'
END
GO

View Replies !

SQL Trigger To Run On Insert Only
Rightio - this is probably a simple question for you SQL afficionados.I have written a trigger to update a master table with a CreateDate field. Simple enough I thought but it updates this field when the existing record is edited as well - not so good.This is how it looks like:CREATE TRIGGER CreateDateON MasterFOR UPDATE ASDECLARE@idMaster intSELECT @idMaster = idMaster from Insertedupdate Masterset CreatedDate = getdate()where idMaster = @idMasterGOWell I know I can write an IF statement that will basically say if the field is not null then only update - fair enough - but is there a simpler way to do this? Is there a way I need to write my CREATE TRIGGER statement that ONLY makes it run when it is a NEW INSERT ONLY?THANKS!

View Replies !

Trigger: Before Insert
Hi, i'm a newbie in writting trigger and stored procedure, need to get help on this:

I cannnot find anyway to run the trigger before insert, anyway to do this?

Thanks.

View Replies !

Trigger INSTEAD OF INSERT??
So... dear friends
I want to insert values to a table without firing the foreign key Constraint.
I think one way is to insert values into the parent table first and then perform the initial insert.
Is this possible with an INSTEAD OF INSERT Trigger or another way? If yes can you make an example? I want to check for dublicate values in the parent table too!!
Thanx!

View Replies !

Insert Trigger
I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?

Tax Detail Table Exchange Table

Account# int Country char
Description char ExchangeRate Money
Amount money
Country char

I am working with MSS 6.5. Any and all help will be greatly appreciated.

Fidencio Peña

View Replies !

ON INSERT TRIGGER
MSSQL 6.5

Is there any way to change some columns on inserted lines in ON INSERT trigger?

My notice: I try to find something about it in SQLBOOKS, but I found nothing.
It's possibly not TRUEEE!!!!! It wants me to grumble.

Thank for your answer.

View Replies !

Pre-insert Trigger
I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.

View Replies !

Insert Trigger
I am having problems with my triggers

I have 2 triggers, a insert and an update.

My insert, copies all the values from the inserted table to a temp table.

MY update deletes any values in the deleted table from the temp table and then inserts all the values from the inserted table into the temp table.

The problem is, when I perform an insert the insert trigger fires and then for some odd reason the update trigger fires as well.

Here is the code for the insert trigger that for some reason seems to firing the update trigger after it is fired..

*************************
insert into T_output_epacodes(output_id, epa_code, last_update, updated_by)
select output_id, epa_code, last_update, updated_by from Inserted

***************************

Any ideas? im baffled.

thanks

View Replies !

Insert Trigger
Should a insert trigger fire my update trigger as well? it fires it automatically after the insert? Is this a bug?

View Replies !

Insert Trigger Help
Hi.

Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.

Any ideas?

Thanks.

Bill

View Replies !

Insert Trigger (Help)
I have to create a insert trigger that calls a stored procedure usp_a
I need to pass two parameters to this stored procedure, value coming from the row inserted...
What is the syntax? Any help will be appreciated?

View Replies !

Trigger On Insert
Hi,

I have SSIS package that takes in data from an excel file and transfers it to a sql 2005 table.

I have a trigger set on the table in the sql 2005 db. However the trigger does not work when the package executes.

Am I missing something ?

View Replies !

Before Insert Trigger
Hi,

I've a SMS gateway that inserts SMS message into a varchar column. However, when it receives a SMS with single quote, it fails to insert into the varchar column. E.g., It's a...How's life ?....

I need help to find a workaround. Is there a equivalent of Before Insert trigger ? Also realised that the locgical 'inserted' table cannot be updated.

Advice pls.

View Replies !

Insert Trigger
I have a table that has a unique ID field. When a new record is
inserted into the table I would like to insert the ID into 3 other
tables. I am new to triggers and am not sure how to handle
this. Any idea how the trigger would be written?

View Replies !

Help With Before Insert Trigger
Hi there

Hoping someone could steer me in the right direction with a trigger I need to create on the database, I've not done one before. Despite reading a lot on the topic, I can't seem to get the syntax right.

I have a table called 'SLOC'. This table has a field called 'Lines_In_New'.
The data to be inserted into 'SLOC' may or may not have a value for the 'Lines_In_New' field.
I only wish to insert the rows that contain a value in this field. So I figured a trigger before insert would do the job.

Something like:

CREATE TRIGGER sloc_trigger
    BEFORE INSERT ON SLOC
    FOR EACH ROW
    IF Lines_In_New is NOT NULL THEN
       insert the row
    END IF;

I probably have this completely wrong, but its the bit inbetween the IF statement that I'm struggling with.

Could anyone help me please, I'd be grateful. Thanks.

View Replies !