Q: Trigger - Reference To Fields In Inserted ?

Dec 20, 2005

Hi all,

I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won. 

I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table.

I find it very difficult to transform the code below to something that looks like the examples found in documentation.

Could someone get me started in the right direction?

Thanks in advance,

Anders

create trigger result_insert on result

after insert as

begin

declare @won1 as int

declare @won2 as int

declare @oldRank1 as float

declare @oldRank2 as float

declare @oldranksum as float



select @won1 = sum(wongames1) from result where player1 = inserted.player1 and player2=inserted.player2

select @won2 = sum(wongames2) from result where player1 = inserted.player1 and player2=inserted.player2



select @oldrank1 = Rank from RankingInfo where memberid = inserted.playerid1

select @oldrank2 = Rank from RankingInfo where memberid = inserted.playerid2



set @oldranksum = @oldrank1 + @oldrank2



update rankingInfo set Rank = @won1 / ( @won1+@won2) * @oldranksum where memberid = inserted.player1

update rankingInfo set Rank = @won2 / ( @won1+@won2) * @oldranksum where memberid = inserted.player2



end

View 1 Replies


ADVERTISEMENT

Auditing:Extracting Changed Fields From Inserted Table

Jan 15, 2007

Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick

View 6 Replies View Related

Reference Table Fields Via A Subscript

Nov 6, 2007

I need a table of seasonally adjusted forecasts. Each row contains 52 weekly values to been named as needed (e.g. Week01, Week02, etc.). At any point in the year, I want to be able to determine how many weeks I can cover with the product on hand.

I have not been able to envision a set based, normalized solution given there are nearly a million items to be evaluated daily. If I normalize, I'm faced with 52 million rows. It seems to me I'm better off bringing a wide row containing all 52 week buckets into memory and working with them in a stored procedure. And if that's the best solution, how do I reference these "virtual" buckets? I'm thinking of some kind of While() loop that counts the weeks until the inventory is exhaused but I need a subscript based technique to reference each week.

Any thoughts?

View 5 Replies View Related

Getting Inserted Data In Trigger

Feb 22, 2007

I am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag 

View 1 Replies View Related

Inserted Row Deletes After Trigger

Oct 22, 2007

I'm hoping someone has seen this before because I have no idea what could be causing it.
 I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.
My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.
The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
     INSERT INTO tblHistory(table_name, record_id, is_insert)     VALUES ('items', 123, 1)
END
tblHistory's field types are (varchar(50), BigInt, bit).
As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?
Thanks in advance!

View 7 Replies View Related

Using Inserted Table In Trigger

Feb 17, 2005

hi, i am writing a trigger to log inserts,updates and deletes in a table and would like to also enter the user details ie who did the transaction. is the spid in the inserted table? if not how do i get this information?

TIA

View 3 Replies View Related

Trigger: Need To Use Inserted In Sp_executesql

Jul 21, 2005

i have a trigger, so need to use the "inserted" table that comes in.

however, i need to use this "inserted" in the following way:


EXEC @LRES = sp_executesql N'
INSERT INTO newtable (col1,col2)
select * from (select acol1,acol2 from inserted WITH (NOLOCK))

however i keep on getting the error that he doesn't know the object "inserted".... I i have the feeling that i may not use inserted at that stage.

what now?

i really need to use the sp_executesql result in the @LRES since after it we should perform some actions in other tables, depending on the result of the @LRES. anyway i really hope anyone can help me as soon as possible.

View 4 Replies View Related

Put Inserted Value Into A Parameter In A Trigger

Sep 6, 2004

How would i get the value of a field that i just inserted and put that into a parameter, so that i could update another table.

This is the code that i used in the trigger that did not work:
@field1 = select srcfield1 from inserted

Anyway here is the full code:

CREATE TABLE Source (srcID int IDENTITY, srcField1 nvarchar(50))
CREATE TABLE Destination (destID int IDENTITY, destField1 nvarchar(50))
go

CREATE TRIGGER tr_SourceInsert ON [dbo].[Source]
FOR INSERT
@Field nvarchar(50) output
AS
SELECT @Field1 = SELECT Field1 FROM inserted
UPDATE Destination
SET Field1 = @Field
where destID = '1'
go

INSERT Source(srcfield1) VALUES ('A')
go

View 3 Replies View Related

How Do I Get A Trigger Just To Copy The Inserted Row.

Feb 8, 2005

HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger:

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp],



How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? Ed

View 2 Replies View Related

Trigger Inserted/Deleted

Mar 26, 2007

Hello,

Is there an alternative to using FETCH to loop through the Inserted/Delete Tables within a trigger? Does this work?

SELECT * FROM Inserted
BEGIN

if INSERTED.IsActive then ...


END

Would this only see the first record?

Currently I'm doing the following;

AS
DECLARE @JobID INTEGER;
DECLARE @IsActive BIT;
DECLARE Temp CURSOR FOR SELECT JobID, IsActive FROM Inserted;
BEGIN
OPEN Temp;
FETCH NEXT FROM Temp INTO @JobID, @IsActive;
WHILE (@@FETCH_STATUS = 0) BEGIN

if @IsActive then ...

FETCH NEXT FROM Temp INTO @JobID, @IsActive;
END;
CLOSE Temp;
DEALLOCATE Temp;

Is this the best method for looping through the Deleted/Inserted or any other table within a trigger?

Thanks,
Steve

View 4 Replies View Related

Joining / Concatenating Fields - Unique Reference?

Jun 13, 2014

I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?

My query and my desired results are below:

select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15

item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014

View 2 Replies View Related

Inserted/Deleted Trigger Tables In SP

Mar 29, 2001

SQL 7 SP2

Are the tables inserted and deleted available from within a sp which is called from a trigger ?

Craig

View 1 Replies View Related

Help! Trigger Using "inserted" Not Inserting Some Records.

Oct 10, 2001

Hello all,

I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:

EventName
EventStatusID
NumberofDays
NumberofStores
PreferredDate1
PleaseContactFlag
EventStoreSelection
EventClusterID

Note: The following fields have their Default Value set to (0)
SystemID
EventStatusID
Coop
NewProductFlag
TVSupportFlag
RadioSupportFlag
FSISupportFlag
RollbackPricing
PleaseContactFlag

Default Value set to (1):
KitInformationID

Here is the trigger:

CREATE TRIGGER EmoesImport ON mc_events FOR INSERT
AS
IF @@ROWCOUNT<>0

BEGIN

SET IDENTITY_INSERT mcweb2.dbo.mc_events ON

DECLARE @SystemID int
DECLARE @EventID int
DECLARE @AccountID int
DECLARE @BillingContactID int
DECLARE @EventName varchar(100)
DECLARE @EventStatusID tinyint
DECLARE @Coop bit
DECLARE @CoopSupplier varchar
DECLARE @SamplesPerDay int
DECLARE @BrochuresPerDay int
DECLARE @AverageDailyMovement int
DECLARE @SalesGoal int
DECLARE @NumberofDays int
DECLARE @NumberofHours int
DECLARE @NumberofStores int
DECLARE @WeekNumber tinyint
DECLARE @PreferredHourID tinyint
DECLARE @PreferredHourother char(20)
DECLARE @PreferredDate1 varchar(20)
DECLARE @PreferredDate2 varchar(20)
DECLARE @NewProductFlag bit
DECLARE @TVSupportFlag bit
DECLARE @RadioSupportFlag bit
DECLARE @FSISupportFlag bit
DECLARE @RollbackPricing bit
DECLARE @PleaseContactFlag bit
DECLARE @EventStoreSelection tinyint
DECLARE @EventClusterID int
DECLARE @KitInformationID tinyint
DECLARE @KitDescription varchar(1000)
DECLARE @KitOther varchar(200)
DECLARE @MCProgNum varchar(7)
DECLARE @rowguid uniqueidentifier

SELECT @SystemID = SystemID FROM INSERTED
SELECT @EventID = EventID FROM INSERTED
SELECT @AccountID = AccountID FROM INSERTED
SELECT @BillingContactID = BillingContactID FROM INSERTED
SELECT @EventName = EventName FROM INSERTED
SELECT @EventStatusID = EventStatusID FROM INSERTED
SELECT @Coop = Coop FROM INSERTED
SELECT @CoopSupplier = CoopSupplier FROM INSERTED
SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED
SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED
SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED
SELECT @SalesGoal = SalesGoal FROM INSERTED
SELECT @NumberofDays = NumberofDays FROM INSERTED
SELECT @NumberofHours = NumberofHours FROM INSERTED
SELECT @NumberofStores = NumberofStores FROM INSERTED
SELECT @WeekNumber = WeekNumber FROM INSERTED
SELECT @PreferredHourID = PreferredHourID FROM INSERTED
SELECT @PreferredHourother = PreferredHourother FROM INSERTED
SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED
SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED
SELECT @NewProductFlag = NewProductFlag FROM INSERTED
SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED
SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED
SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED
SELECT @RollbackPricing = RollbackPricing FROM INSERTED
SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED
SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED
SELECT @EventClusterID = EventClusterID FROM INSERTED
SELECT @KitInformationID = KitInformationID FROM INSERTED
SELECT @KitDescription = KitDescription FROM INSERTED
SELECT @KitOther = KitOther FROM INSERTED
SELECT @MCProgNum = MCProgNum FROM INSERTED
SELECT @rowguid = rowguid FROM INSERTED

INSERT INTO mcweb2.dbo.mc_events
(SystemID,
EventID,
AccountID,
BillingContactID,
EventName,
EventStatusID,
Coop,
CoopSupplier,
SamplesPerDay,
BrochuresPerDay,
AverageDailyMovement,
SalesGoal,
NumberofDays,
NumberofHours,
NumberofStores,
WeekNumber,
PreferredHourID,
PreferredHourother,
PreferredDate1,
PreferredDate2,
NewProductFlag,
TVSupportFlag,
RadioSupportFlag,
FSISupportFlag,
RollbackPricing,
PleaseContactFlag,
EventStoreSelection,
EventClusterID,
KitInformationID,
KitDescription,
KitOther,
MCProgNum,
rowguid)
VALUES
(@SystemID,
@EventID,
@AccountID,
@BillingContactID,
@EventName,
@EventStatusID,
@Coop,
@CoopSupplier,
@SamplesPerDay,
@BrochuresPerDay,
@AverageDailyMovement,
@SalesGoal,
@NumberofDays,
@NumberofHours,
@NumberofStores,
@WeekNumber,
@PreferredHourID,
@PreferredHourother,
@PreferredDate1,
@PreferredDate2,
@NewProductFlag,
@TVSupportFlag,
@RadioSupportFlag,
@FSISupportFlag,
@RollbackPricing,
@PleaseContactFlag,
@EventStoreSelection,
@EventClusterID,
@KitInformationID,
@KitDescription,
@KitOther,
@MCProgNum,
@rowguid)

SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF

END


TIA,

Bruce Wexler
Programmer/Analyst
IT Department
Mass Connections
Ph: (562) 365-0200 x1091
Fx: (562) 365-0283
http://www.massconnections.com

View 1 Replies View Related

Trigger, Inserted Pseudo Table?

Jul 1, 2004

I am trying to create a trigger on a table but when I check the syntax it
tells me that "The column prefix 'inserted' does not match with a table name or alias used in this query"


CREATE TRIGGER trg_Structural_GenerateBarcode ON [dbo].[tbStructuralComponentSchedule]
AFTER INSERT
AS
DECLARE @iCount int, @cBarcode char (25), @cCode char(4)
DECLARE @cProject char(7), @cComponent char(10), @iEntryID int

SELECT @cProject = inserted.fkProjectNumber, @cComponent = inserted.fkComponentID, @iEntryID = inserted.EntryID

.......
GO


How do I use the inserted pseudo table?

Mike B

View 2 Replies View Related

Check For Nulls Being Inserted Via Trigger

Aug 7, 2014

I want to check when a null value is being inserted in the column in my table using my existing trigger.

At the moment i have eliminated duplicates, but instead of saving the duplicate a null value is placed into the column if there is a duplicate entry.

View 1 Replies View Related

Trigger- Get Row Number From Inserted/deleted

Jul 19, 2006

I need to add the row number or record number to the 'inserted' and 'deleted' tables in a trigger.

Among other things, I have tried-

SELECT 1 as rowId, i.* INTO #ins FROM inserted i
if @@ROWCOUNT > 1 --if multiple rows, set row number
begin

SELECT @pkWhere = coalesce(@pkWhere + ' and ', ' where ') + PKF.colName + ' <= i.' + PKF.colName FROM #primaryKeyFields PKF
set @strSQL = 'update #ins set rowId = (Select Count(*) From #ins i' + @pkWhere + ')'

exec (@strSql)

end

-the above sets rowId for every record to the total instead of sequential. Keep in mind that this code is used to create a trigger for any table, hence I cannot specify any column names literally.

This SHOULD be simple... right?

View 17 Replies View Related

How To Use Table Inserted Within Exec In A Trigger?

Mar 17, 2008

I like to use the table "Inserted" within exec(), but it doesn't work because the scope is different. Does anyone have some sort of solution to this problem? The reason I am doing it this way is because I have a table consist of 200+ columns of bit types that contains permission information (The worest design i have ever seen!).






Code Snippet
--gather column names


declare @ScreenPermissions nvarchar(256)


declare c_Permission cursor
for
SELECT [name]
FROM syscolumns
WHERE id = (
SELECT id FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'ScreenPermissions'
)
and [name] like 'Allow%'



open c_Permission
fetch next from c_Permission
into @ScreenPermissions
while @@fetch_status = 0
begin

exec('INSERT INTO EmployeeInRoles (EmployeeID, RoleID) ' +
'select i.EmployeeID, r.RoleID ' +
'from inserted as i ' +
' inner join ScreenPermissions AS sp on sp.EmployeeID = i.EmployeeID and sp.' + @ScreenPermissions + ' = 1 ' +
' inner join Roles AS r on r.LoweredRoleName = Lower(' + '''' + @ScreenPermissions + '''' + ')' )

fetch next from c_Permission
into @ScreenPermissions
end
close c_Permission
DEALLOCATE c_Permission

View 7 Replies View Related

Trigger To Indicate The Row Has Been Changed (updated Or Inserted)

Aug 30, 2007

Hi,

We have a column syncUpdate in some tables and we need a trigger (or one for each table) which will set the current dateTime for the syncLastUpdate (dateTime) when either the row is inserted or updated (we have to ignore the syncLastUpdate column itself as this would be an infinite loop, I think).

I don't know much about DB but I think that is easly doable.

Can anyone help me with that, please?

Cheers

View 3 Replies View Related

How Is It Possible That Inserted And Deleted Are Both Empty In Trigger?

Oct 30, 2006

I created manage update trigger to react on one column changes. There is an application which is working with DB, so I don't have access to SQL query which changes this column. In most cases trigger works fine, but in one case when this column changes, trigger is fired and IsUpdatedColumn is true for this column, but both inserted and deleted table are empty, so I can't get new value for this column. Any idea why is it happened? Is any way around?

This column type is uniqueidentifier. Inserted and deleted tables are empty when application is changing value from NULL to not null value, but if I change it myself from Management Studio inserted table contains right values. Most like problem is in query which is changing that value.

I'm doing that on Sql Server 2005.

View 1 Replies View Related

Composing A Reference From Fields Located In Mutiple Tables

Jun 12, 2007

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

View 2 Replies View Related

Viewing The &#34;inserted&#34; Temp Table During Trigger?

Apr 6, 2001

Having probs debugging trigger ... need to view the "inserted" table contents, how do I do this? Manuals are pants, any tricks folks?

View 1 Replies View Related

Trigger On XML Column - Generate Mail If Particular Value Get Inserted In A Tag

Feb 26, 2014

We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular value get inserted in a tag.

For ex:

<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">

In the above xml data if we have the value 21545 in Stab the trigger needs to be executed and mail needs to be sent to a distribution list.

The Trigger should not be initiated if value other than 21545 is updated...

View 4 Replies View Related

Problem With My Trigger(inserted/deleted-tables)

Nov 9, 2007



I dont know what I am doing wrong. The trigger (see below) is doing what I want it to do when I do this:


INSERT INTO dbo.personal

(personal_id, chef_id,fornamn, efternamn)

VALUES

(40, 100, 'Malin', 'Markusson' , 'Boss')



but when I remove one value, the result in the logtable is NULL:



INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)

VALUES

(40, 100, 'Malin', 'Markusson' )

How can I change the trigger so that it will give me the information of the values that have been updated, inserted or deleted when I dontchange all values (just a couple of them)?


My trigger:
CREATE Trigger trigex

ON dbo.personal

FOR insert, update,delete

AS

INSERT INTO logtable (Innan_värde, Ny_värde)

SELECT

rtrim(cast(d.personal_id as varchar)+', '+cast(d.chef_id as varchar)+', '+rtrim(d.efternamn)+', '+ rtrim(d.fornamn)+ ', '+ rtrim(d.titel)),

(cast(i.personal_id as varchar)+', '+cast(i.chef_id as varchar)+', '+rtrim(i.efternamn)+', '+ rtrim(i.fornamn)+ ' '+ rtrim(i.titel))

FROM inserted i full join deleted d on i.personal_id = d.personal_id


My table:
CREATE Table logtable

(Innan_värde varbinary(max),

Ny_värde varbinary(max))






Thank you !

View 1 Replies View Related

Create A Trigger To Update A Row That's Been Inserted Or Updated

Jun 4, 2007

Hi



Apologies if this is a silly question



I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData



After I insert or Update a row in this table I need to update the SearchData column

with



UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)



only for that Id



I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.



Regards



David

View 4 Replies View Related

View, SP, & Trigger Reference

Nov 2, 1998

Can anyone recommend a good reference book on views, stored procedures (system stored
procedures as well) and triggers? I am in need of assistance in these areas.

Thanks!
Toni

View 2 Replies View Related

Trigger To Generate Mail If Particular Kind Of Data Get Inserted In A Tag

Feb 25, 2014

We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular kind of data get inserted in a tag.

For ex:

<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">

In the above xml data if we have the value 21545 in Stab tag the trigger needs to be executed and mail needs to be sent to a distribution list.

View 1 Replies View Related

Order Of Records In The INSERTED/DELETED Tables In A Trigger

Nov 29, 2007

We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.

Thanks!
CB

View 1 Replies View Related

Trigger Problem, Comparing Deleted/inserted Not Working :(

Sep 14, 2007

Hello all,

I have I trigger where I want to insert all _changed_ rows from the INSERTED table into
a table called tempProducts.

If I put this query inside my trigger, I selects exactly the rows I want: rows changed




Code SnippetSELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED




I the current trigger I have




Code SnippetINSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM INSERTED




but this inserts ALL updated rows, not only the changed ones.

So I thought hey, I´ll just combine the two querys and the problem will be solved, like so:



Code Snippet

INSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM (SELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED) as Temp




But for some reason, this won´t work! Why is this? What am I doing wrong?

View 10 Replies View Related

SQL Server 2012 :: Trigger Inserted Multiple Rows After Insert

Aug 6, 2014

I create a Trigger that allows to create news row on other table.

ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS

[code]...

But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001

I search a means to have 50 lines differently on my CONTACT table.

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005

View 9 Replies View Related

SQL Server 2012 :: How To Get A Table Identity Inserted By Instead Of Insert Trigger

May 14, 2015

I have a problem described as follows: I have a table with one instead of insert trigger:

create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage

[code]....

When I execute P1 it returns 0 for Id field of @T1.

How can I get the Identity in this case?

PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.

View 5 Replies View Related

Can I Debug/watch On The Trigger's INSERTED And DELETED Records/values?

Jan 25, 2006

When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you

View 11 Replies View Related

Reference For Newbie On Trigger And Store Procedure

Jan 10, 2006

hi, good day, i new in trigger and store procedure, can someone please suggest some advice and reference material in order to mastering trigger and store procedure ? thank you

View 1 Replies View Related

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

Nov 4, 2015

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)

Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved