triggers

2 Sep 2005 8:21 PM

greg

hello,
i have a basic question about triggers. are they created the same way you
create stored procedures? right click on the stored procedure and pick new
stored procedure?
i created a new stored procedure and inserted this text:
CREATE TRIGGER trig_sequence_number
ON acd_document_s
FOR INSERT
AS
SELECT object_name
from acd_document_s

i get this error:
error 21001. stored procedure defiition must include name and text or
libraryame.

can anyone help?

2 Sep 2005 8:48 PM

Alejandro Mesa

Greg,

Use QA instead.

use yourDB
go

CREATE TRIGGER trig_sequence_number
ON acd_document_s
FOR INSERT
AS
-- SELECT object_name
-- from acd_document_s

return
go

Do not return rows to the client app from a trigger.

AMB

Show quote

"greg" wrote:

> hello,
> i have a basic question about triggers. are they created the same way you
> create stored procedures? right click on the stored procedure and pick new
> stored procedure?
> i created a new stored procedure and inserted this text:
> CREATE TRIGGER trig_sequence_number
> ON acd_document_s
> FOR INSERT
> AS
> SELECT object_name
> from acd_document_s
>
> i get this error:
> error 21001. stored procedure defiition must include name and text or
> libraryame.
>
> can anyone help?
>
>
>

6 Sep 2005 5:43 PM

greg

thanks for the help.
i really dont want to return results.
i would like to get the new row (Insert) and add a value into one of the
columns.
would i still use QA?
If i save it as query file, will it then automaticall start running when a
new row is added?
How can i get the new row and add into column X the value "New"?

thanks for you help

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:B1419492-FB8F-4F93-9460-D7D84666B4E2@microsoft.com...
> Greg,
>
> Use QA instead.
>
> use yourDB
> go
>
> CREATE TRIGGER trig_sequence_number
> ON acd_document_s
> FOR INSERT
> AS
> -- SELECT object_name
> -- from acd_document_s
>
> return
> go
>
> Do not return rows to the client app from a trigger.
>
>
> AMB
>
> "greg" wrote:
>
>> hello,
>> i have a basic question about triggers. are they created the same way
>> you
>> create stored procedures? right click on the stored procedure and pick
>> new
>> stored procedure?
>> i created a new stored procedure and inserted this text:
>> CREATE TRIGGER trig_sequence_number
>> ON acd_document_s
>> FOR INSERT
>> AS
>> SELECT object_name
>> from acd_document_s
>>
>> i get this error:
>> error 21001. stored procedure defiition must include name and text or
>> libraryame.
>>
>> can anyone help?
>>
>>
>>

6 Sep 2005 7:20 PM

Hugo Kornelis

On Tue, 6 Sep 2005 12:43:05 -0500, greg wrote:

>thanks for the help.
>i really dont want to return results.
>i would like to get the new row (Insert) and add a value into one of the
>columns.
>would i still use QA?
>If i save it as query file, will it then automaticall start running when a
>new row is added?

Hi Greg,

To make sure that some code is executed when a row is added, you have to
create a trigger.

To create a trigger, you should use Query Analyzer. Type or paste the
code to create the trigger, or open a file withb the saved code, then
execute the code. That creates the trigger.

>How can i get the new row and add into column X the value "New"?

I'd use a default, not a trigger.

But if you want to do it in a trigger, then use something like this:

CREATE TRIGGER MyTrigger
ON MyTable FOR INSERT
AS
UPDATE MyTable
SET    X = 'New'
WHERE EXISTS
(SELECT *
FROM   inserted
WHERE MyTable.KeyColumn = inserted.KeyColumn)
go

Replace "KeyColumn" with the name of the column that is the primary key
for your table. If you have a compound primary key, change the subquery
to
(SELECT *
FROM   inserted
WHERE MyTable.KeyColumn1 = inserted.KeyColumn1
AND    MyTable.KeyColumn2 = inserted.KeyColumn2
AND    .....)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

6 Sep 2005 7:27 PM

greg

thanks for the help.
i will take a look

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:2nqrh1h3mrn8u3u3kjedqa3usuc8iogpcm@4ax.com...
> On Tue, 6 Sep 2005 12:43:05 -0500, greg wrote:
>
>>thanks for the help.
>>i really dont want to return results.
>>i would like to get the new row (Insert) and add a value into one of the
>>columns.
>>would i still use QA?
>>If i save it as query file, will it then automaticall start running when a
>>new row is added?
>
> Hi Greg,
>
> To make sure that some code is executed when a row is added, you have to
> create a trigger.
>
> To create a trigger, you should use Query Analyzer. Type or paste the
> code to create the trigger, or open a file withb the saved code, then
> execute the code. That creates the trigger.
>
>>How can i get the new row and add into column X the value "New"?
>
> I'd use a default, not a trigger.
>
> But if you want to do it in a trigger, then use something like this:
>
> CREATE TRIGGER MyTrigger
> ON MyTable FOR INSERT
> AS
> UPDATE MyTable
> SET    X = 'New'
> WHERE EXISTS
> (SELECT *
> FROM   inserted
> WHERE MyTable.KeyColumn = inserted.KeyColumn)
> go
>
> Replace "KeyColumn" with the name of the column that is the primary key
> for your table. If you have a compound primary key, change the subquery
> to
> (SELECT *
> FROM   inserted
> WHERE MyTable.KeyColumn1 = inserted.KeyColumn1
> AND    MyTable.KeyColumn2 = inserted.KeyColumn2
> AND    .....)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

6 Sep 2005 8:17 PM

greg

thanks all.
got it working. for others who might want an example:

CREATE TRIGGER MyTrigger
ON acd_document_s FOR INSERT
AS
UPDATE acd_document_s
SET template_version = '2'
WHERE EXISTS
(SELECT *
FROM inserted
WHERE acd_document_s.r_object_id = inserted.r_object_id)

Show quote

"greg" <greg@nospam.com> wrote in message
news:%23CIp6ixsFHA.2348@tk2msftngp13.phx.gbl...
> thanks for the help.
> i will take a look
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:2nqrh1h3mrn8u3u3kjedqa3usuc8iogpcm@4ax.com...
>> On Tue, 6 Sep 2005 12:43:05 -0500, greg wrote:
>>
>>>thanks for the help.
>>>i really dont want to return results.
>>>i would like to get the new row (Insert) and add a value into one of the
>>>columns.
>>>would i still use QA?
>>>If i save it as query file, will it then automaticall start running when
>>>a
>>>new row is added?
>>
>> Hi Greg,
>>
>> To make sure that some code is executed when a row is added, you have to
>> create a trigger.
>>
>> To create a trigger, you should use Query Analyzer. Type or paste the
>> code to create the trigger, or open a file withb the saved code, then
>> execute the code. That creates the trigger.
>>
>>>How can i get the new row and add into column X the value "New"?
>>
>> I'd use a default, not a trigger.
>>
>> But if you want to do it in a trigger, then use something like this:
>>
>> CREATE TRIGGER MyTrigger
>> ON MyTable FOR INSERT
>> AS
>> UPDATE MyTable
>> SET    X = 'New'
>> WHERE EXISTS
>> (SELECT *
>> FROM   inserted
>> WHERE MyTable.KeyColumn = inserted.KeyColumn)
>> go
>>
>> Replace "KeyColumn" with the name of the column that is the primary key
>> for your table. If you have a compound primary key, change the subquery
>> to
>> (SELECT *
>> FROM   inserted
>> WHERE MyTable.KeyColumn1 = inserted.KeyColumn1
>> AND    MyTable.KeyColumn2 = inserted.KeyColumn2
>> AND    .....)
>>
>> Best, Hugo
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>