Trigger Doesn't Work

Arsi · December 6th, 2004, 02:37 PM

Hello,

I'm begging for some help...Please. I am about to go crazy trying to figure out what the problem is with my trigger. It works fine on non-Sharepoint databases. I don't know enough about Sharepoint to know what the problem is. I have written a trigger to insert data into another database on the same server whatever data is inserted into the sharepoint database. The trigger is on the UserData table in the Sharepoint database. Here is the trigger:

Create Trigger [Insert_db] ON [dbo].[UserData]
FOR INSERT AS
INSERT INTO dbTimeSheet..ProjectInfo(BidNumber, ProjectName)
SELECT i.nvarchar2, i.nvarchar1
FROM INSERTED i

Here is the error:
The list item could not be added because the length of the fields was too long. Please shorten the new entries or remove some fields from this list.

Is it talking about the fields in the database or the fields in the Sharepoint form?

The insert works when its just inserting into the Sharepoint database, but not when the trigger is added to the table. Also, because there is an error, it does not allow any of the users to use the Sharepoint sites for any inserting, updating or deleting. Please help.

*******(*)*******

*******(*)*******

jemacc · December 7th, 2004, 02:49 AM

Create Trigger [Insert_db] ON [dbo].[UserData]
FOR INSERT AS
INSERT ProjectInfo(BidNumber, ProjectName)
SELECT i.nvarchar2, i.nvarchar1
FROM INSERTED i

Jaime E. Maccou

Arsi · December 7th, 2004, 12:54 PM

Hello Jaime,

Thank you for your post. I really appreciate it. I tried the trigger changes you suggested and I am getting a new Sharepoint error which says "SQL Server Error: SQL Server may not be started." It is started. Also, how does the trigger know to insert it into the second database (dbTimeSheet)? The trigger is on the UserData Table on a different database. Do you have any more suggestions? Thanks Again.

*******(*)*******

jemacc · December 7th, 2004, 05:45 PM

? - Is the other database column and data size the same?

Jaime E. Maccou

Arsi · December 8th, 2004, 11:42 AM

Yes everything is the same. Also, I don't know if it matters but there are ntext fields in the sharepoint database that i am not using in my query. I read somewhere that it causes errors?

*******(*)*******

jemacc · December 8th, 2004, 07:14 PM

Out curiosity use an INSTEAD OF TRIGGER

Code:

Create Trigger [Insert_db] ON [dbo].[UserData] 
instead of insert
as 
INSERT INTO dbTimeSheet..ProjectInfo(BidNumber, ProjectName) 
SELECT i.nvarchar2, i.nvarchar1 
FROM INSERTED

Jaime E. Maccou

Arsi · December 9th, 2004, 03:14 PM

Ok, I don't know what happened, but I deleted the orginial trigger tried your suggestion, then deleted it and tried my original code again and they were inserted!!! I was very excited. But now, I am able to insert the bid number and project name but when i add 3 more columns to the query it gives me the same error again. I added 3 columns, 2 with nvarchar datatype and the other with a float datatype. I will give your suggestion a try. Thanks.

*******(*)*******

jemacc · December 13th, 2004, 04:49 PM

Added this information to your original query

Code:

Create Trigger [Insert_db] ON [dbo].[UserData] 
FOR INSERT AS 
INSERT INTO dbTimeSheet..ProjectInfo(BidNumber, ProjectName) 
SELECT i.nvarchar2, i.nvarchar1 
FROM INSERTED i 
Inner Join DB1..table1 As T1 On Inserted.BidNumber = T1.BidNumber
Inner Join DB2..table2 As T2 On Inserted.BidNumber = T1.BidNumber

Jaime E. Maccou

Arsi · December 13th, 2004, 05:04 PM

Ok Great! Thanks that worked! Thanks for all your help!

*******(*)*******