August 6, 2014 at 3:52 am
Hi,
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
BEGIN
DECLARE @ACCOUNTIDvarchar(255),
@accountvarchar(255),
@ADDRESSIDvarchar(255),
@CONTACTIDvarchar(255),
SELECT @ACCOUNTID = ACCOUNTID,
@account = ACCOUNT,
FROMInserted
BEGIN
@ADDRESSID = (Calculation of AddressID)
@CONTACTID = (Calculation of ContactID)
-- Creation Adress linked at contact
INSERT INTO SYSDBA.ADDRESS (addressid, entityid)
VALUES (@AddressID, @ContactID)
-- Creation Contact linked at adress
INSERT INTO sysdba.contact ( addressid, accountid, account, contactid)
VALUES (@AddressID, @ACCOUNTID, @account, @ContactID)
END
END
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
...
An idea please ?
Thanks
August 6, 2014 at 4:07 am
Because your trigger assumes there will be one row in inserted. There won't, there will be one row for each row inserted.
Discard the variables, remove the SELECT @var = ... from inserted and rewrite the rest as insert queries which work off inserted, using the INSERT ... SELECT form
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 4:20 am
Thanks for your reply.
As that :
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS
BEGIN
SELECT ACCOUNTID,
ACCOUNT,
FROMInserted
BEGIN
@ADDRESSID = (Calculation of AddressID)
@CONTACTID = (Calculation of ContactID)
-- Creation Adress linked at contact
INSERT INTO SYSDBA.ADDRESS (addressid, entityid)
VALUES (@AddressID, @ContactID)
-- Creation Contact linked at adress
INSERT INTO sysdba.contact ( addressid, accountid, account, contactid)
SELECT @AddressID, ACCOUNTID, ACCOUNT, @ContactID
FROM Inserted
END
END
August 6, 2014 at 4:32 am
You need to remove the variables, remove the select from inserted, do whatever calculation of addressid and contact ID in the insert statements and use the INSERT ... SELECT form of the insert
p.s. that wouldn't have even passed a syntax check
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 4:47 am
Triggers fire PER BATCH, NOT PER ROW!! If an action in SQL Server affects 100 rows every trigger you create MUST be able to process ALL 100 rows - period.
SQL Server triggers that process one row at a time like yours is one of the most devastatingly bad TSQL problems I come across at clients. It is thus because a) it is easy to do and b) it gives the wrong result with no warning or error, which is by FAR the worst thing that can happen when processing data (on any platform/app). I have actually had a client that went out of business because of it - despite my telling them EXTREMELY bluntly and directly that they would go out of business if they didn't change all their triggers!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 6, 2014 at 6:04 am
Okay, but with your solution I have a problem.
ContactID and AdressID is my Primary Kay.
So with this solution, I have this Result :
C001 - AD001 - AC001 - ACCOUNT 001
C001 - AD001 - AC002 - ACCOUNT 002
C001 - AD001 - AC003 - ACCOUNT 003
C001 - AD001 - AC004 - ACCOUNT 004
C001 - AD001 - AC005 - ACCOUNT 005
:/
August 6, 2014 at 6:11 am
And?
If that's not what you want (which is not at all clear), you need to debug and fix whatever's wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 6:23 am
And I want that ContactID and AdressID is only (Primary Key), as that :
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
My calcul of ContactID, for exemple :
DECLARE @ContactID varchar(20), @CountContactID integer
SELECT @CountContactID = MAX(substring(ContactID,7,7)) + 1
FROM SYSDBA.CONTACT
WHERE ContactID like 'C6UJ9Z%'
AND LEN(ContactID)= 12
IF (@CountContactID < 10)
SET @ContactID = 'C00000' + CONVERT(varchar(20),@CountContactID)
ELSE IF (@CountContactID < 100)
SET @ContactID = 'C0000' + CONVERT(varchar(20),@CountContactID)
ELSE IF (@CountContactID < 1000)
SET @ContactID = 'C000' + CONVERT(varchar(20),@CountContactID)
ELSE IF (@CountContactID < 10000)
SET @ContactID = 'C00' + CONVERT(varchar(20),@CountContactID)
ELSE IF (@CountContactID < 100000)
SET @ContactID = 'C0' + CONVERT(varchar(20),@CountContactID)
ELSE
SET @ContactID = 'C6UJ9Z' + CONVERT(varchar(20),@CountContactID)
I generate a unique identifier. For exemple, @CountContactID = 198
So my @ContactID of my FIRST ROW is C000198.
But for the second,.. rows, it should be C000199, C000200,.. and not C000198 for all my rows.
It don't exist a means to create an autoincrement on my SELECT ? and replace my Calculation by this autoincrement 🙂
August 6, 2014 at 6:26 am
Valbuenito (8/6/2014)
It don't exist a means to create an autoincrement on my SELECT ?
Look up ROW_NUMBER()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 6:37 am
Perfect !
Thnaks U very much ! 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply