July 15, 2004 at 11:28 am
When I issue:
CREATE TRIGGER UTR_Duplicate_REQ
ON lawprod.REQHEADER FOR INSERT
AS
BEGIN
I receive a message:
Server: Msg 2714, Level 16, State 5, Procedure UTR_Duplicate_REQ, Line 65535
There is already an object named 'UTR_Duplicate_REQ' in the database.
However when I issue:
drop trigger UTR_Duplicate_REQ
go
I receive a message:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'UTR_Duplicate_REQ', because it does not exist in the system catalog.
Why the first message references line 65535 (I don't have so many lines) and
what is wrong? How to drop the trigger?
Thank you very much in advance,
Alex
July 15, 2004 at 11:59 pm
UTR_Duplicate_REQ might be something other than a trigger
run the below query and see what you get for xtype
select xtype from sysobjects where name='UTR_Duplicate_REQ'
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
July 16, 2004 at 7:04 am
It's my trigger, nothing else.
July 16, 2004 at 7:37 am
What xtype did you get? If it's TR, then I don't know what's wrong. If that's the case, then maybe you could post the entire script (if it's not too long)
Other thing I noticed is that the naming for the table the trigger is to be created on is wrong. You said
CREATE TRIGGER UTR_Duplicate_REQ
ON lawprod.REQHEADER FOR INSERT
The table should be refered to either just by name or if you need the db, by a 3 part naming database.owner.table
If the table is owned by dbo, the create should read
CREATE TRIGGER UTR_Duplicate_REQ
ON lawprod.dbo.REQHEADER FOR INSERT
If it's owned by someone other than dbo, then alter the owner accordingly
Hope that's of some help.
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
July 16, 2004 at 7:54 am
Thanks for your help.
lawprod is the owner of the table and UTR_Duplicate_REQ is a trigger as per sysobjects, so nothing is wrong in the syntax. Here is the script:
CREATE TRIGGER UTR_Duplicate_REQ
ON lawprod.REQHEADER FOR INSERT
AS
BEGIN
DECLARE @v_req_number VARCHAR(14),
@v_l_index CHAR(4),
@v_line_nbr INT,
@v_atchnbr CHAR(2),
@v_operator_id VARCHAR(10),
@v_vendor VARCHAR(9),
@v_ssn VARCHAR(12),
@v_newssn VARCHAR(12),
@v_req_date DATETIME,
@v_item VARCHAR(32),
@v_print VARCHAR(500)
IF EXISTS (SELECT name FROM sysobjects WHERE id =
object_id(N'dbo.newreq') and OBJECTPROPERTY(id,
N'IsTable') = 1)
DROP TABLE dbo.newreq
CREATE TABLE newreq (ssn VARCHAR(12),
item VARCHAR(32), line_nbr int,
req_number VARCHAR(14), req_date DATETIME)
/* D E C L A R E C U R S O R #1*/
DECLARE c_myparser1 CURSOR FOR
SELECT INS.REQ_NUMBER, INS.CREATION_DATE, INS.VENDOR, INS.L_INDEX, INS.OPERATOR_ID,
RLI.ITEM, RLI.LINE_NBR,
dbo.UF_SSN(H.OBJECT)
FROM inserted INS,
lawprod.REQLINE RLI,
lawprod.L_HPLI H
WHERE INS.VENDOR='10708' AND RLI.REQ_NUMBER=INS.REQ_NUMBER AND H.L_INDEX = INS.L_INDEX
AND LTRIM(RTRIM(SUBSTRING(H.OBJECT, (CHARINDEX(',',H.OBJECT) -
1),1))) = 'P'
/* O P E N C U R S O R #1 */
OPEN c_myparser1
FETCH NEXT FROM c_myparser1
INTO @v_req_number,
@v_req_date,
@v_vendor,
@v_l_index,
@v_operator_id,
@v_item,
@v_line_nbr,
@v_ssn
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO newreq (ssn, item, line_nbr, req_number, req_date)
VALUES (@v_ssn, @v_item, @v_line_nbr, @v_req_number, @v_req_date)
FETCH NEXT FROM c_myparser1
INTO @v_req_number,
@v_req_date,
@v_vendor,
@v_l_index,
@v_operator_id,
@v_item,
@v_line_nbr,
@v_ssn
END
CLOSE c_myparser1
DEALLOCATE c_myparser1
/* D E C L A R E C U R S O R #2 */
DECLARE c_myparser2 CURSOR FOR
SELECT RH.REQ_NUMBER, RH.CREATION_DATE, RH.VENDOR, RH.L_INDEX, RH.OPERATOR_ID,
RLI.ITEM, RLI.LINE_NBR,
dbo.UF_SSN(H.OBJECT), N.ssn
FROM lawprod.REQHEADER RH,
lawprod.REQLINE RLI,
lawprod.L_HPLI H,
newreq N
WHERE RH.VENDOR='10708' AND RLI.REQ_NUMBER=RH.REQ_NUMBER AND H.L_INDEX = RH.L_INDEX
AND dbo.UF_SSN(H.OBJECT)=N.ssn AND RLI.ITEM=N.item AND (DATEDIFF(day, RH.CREATION_DATE, getdate()) < 270)
AND LTRIM(RTRIM(SUBSTRING(H.OBJECT, (CHARINDEX(',',H.OBJECT) -
1),1))) = 'P'
/* O P E N C U R S O R #2 */
OPEN c_myparser2
FETCH NEXT FROM c_myparser2
INTO @v_req_number,
@v_req_date,
@v_vendor,
@v_l_index,
@v_operator_id,
@v_item,
@v_line_nbr,
@v_ssn,
@v_newssn
WHILE (@@FETCH_STATUS = 0)
BEGIN
/*================= PRINT REQ records which are no more than 9 month old ============ */
PRINT @v_ssn+@v_item+@v_line_nbr+@v_req_number+@v_req_date+@v_operator_id
FETCH NEXT FROM c_myparser2
INTO @v_req_number,
@v_req_date,
@v_vendor,
@v_l_index,
@v_operator_id,
@v_item,
@v_line_nbr,
@v_ssn,
@v_newssn
END
END
What could be wrong is that is that there are no commands at the end:
CLOSE c_myparser2
DEALLOCATE c_myparser2
but I don't understand why it could create the problem of the kind...
July 19, 2004 at 4:50 am
Sorry for the misunderstanding about syntax. I'm not used to seeing tables owned by anyone other than dbo.
Have a look here http://support.microsoft.com/default.aspx?scid=kb;en-us;827448 (MS knowledge base article 827448). See if that might be your problem.
Additionally I can suggest some things to simplify that trigger if you're interested.
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
July 19, 2004 at 10:13 am
I had tried the script recommended by MS a long time before posting my questions. It appears to be addressing completely different issue. I see my trigger in the DB (sysobjects), but can't drop it. It has to have a simple explanation.
I'll greatly appreciate any trigger enhancements you have.
Thank you
March 10, 2006 at 11:22 am
GilaMonster,
Thanks for pointing out the MS article, having an issue on a stage box that this cleared up a treat.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply