Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, Julia
The net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
--
David Gugick
Imceda Software
www.imceda.com

Allowing an exception to a trigger

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

Hello Echo88,

Echo88:

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

A short answer is, yes, you would solve this with an additional column.

A slightly more detailed answer would be, check your "architecture" because your access paths here are "imbalanced". I mean, it sounds like sometimes you directly write to the table, sometimes you write through the stored procedure. My advice is go with stored procedures only. Great chance is your trigger's logic here really belongs to its own procedure which you would call instead of straight writing to the table.

Hope this makes sense. -LV

|||

Thanks for the advice. All writes to any table are done through stored procedures, except triggers because I need to access the trigger tables, but I do it for code maintenance reasons - it just simply easier for to pack it up that way.
Anyway, I am not in love with adding columns after my "architecture" has already been established and I found a better to handle the conditional update. I remember that MS SQL handles UPDATES by placing the new row in the INSERTED trigger table and the old row in the DELETED. I simply just compared the two rows and obtained the results I needed.

All about Triggers...

Created my first trigger a day or two ago that inserts the current date
into a smalldatetime field WHEN another field is updated. My question:
why is it that one cannot update that field a second time using my
website front end? It's as if you enter in that field's info once and
then you're stuck with it... :/ All the other fields may be modified
at will except that one.
My trigger:
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
IF UPDATE(test)
UPDATE lla
SET test_date=GETDATE()
WHERE doc+poe IN (SELECT doc+poe FROM inserted)
END
Any ideas?Try,
...
UPDATE
a
SET
a.test_date=GETDATE()
from
lla as a
inner join
inserted as i
on a.doc = i.doc and a.poe = i.poe
AMB
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>|||Since this is an UPDATE trigger any change you make to the column Test will
also overwrite Test_date. Maybe you just wanted a DEFAULT value for the
column? Example:
ALTER TABLE lla
ADD CONSTRAINT df_lla_test_date
DEFAULT CURRENT_TIMESTAMP FOR test_date
In your UPDATE:
...
WHERE doc+poe IN
This seems unlikely to be the correct or best way to correlate with the
Inserted table. Is (Doc,Poe) the key? If so:
UPDATE lla SET test_date = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM Inserted AS I
WHERE I.doc = lla.doc
AND I.poe = lla.poe)
Concatenating (or adding!?) the two columns would otherwise not give your
query the full benefit of an index on these columns. Concatenating VARCHAR
columns in this way could also give you incorrect results.
David Portas
SQL Server MVP
--|||The UPDATE() function checks to see if the column is in the update
statement. It does NOT check to see if the value is changing from the
original value. I assume when you try your update statement from the
front-end to set the Test_Date to something expilicit it still sets it to
GetDate(). You may need something like this for your trigger. I'm assuming
your primary key is a combination of Doc and Poe...
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
UPDATE lla SET
lla.test_date = GETDATE()
FROM lla
INNER JOIN Inserted I ON lla.Doc = I.Doc AND lla.Poe = I.Poe
INNER JOIN Deleted D ON I.Doc = D.Doc AND I.Poe = D.Poe
WHERE I.Test <> D.Test
END
Paul
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>