Insert Trigger Using Variables Help

Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.

Use database_testing

IF EXISTS (SELECT name FROM sysobjects
WHERE type = 'TR' AND name = 'Trigger_Name')
DROP TRIGGER Trigger_Name
GO

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
Declare @.resource_id int = inserted.resource
@.type = varchar(100) = inserted.type
@.date_logged (datetime) = inserted.creation_date
@.created varchar(100) = inserted.username

Insert into table_A (resource_id, resource_type, date_created, created_by)
values (resource_id,type, date_logged, created)You didn't specified that exactly what you want to fulfill. I think you want to track users for insert row or update row.

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS

SET NOCOUNT ON

Begin
Insert into table_A (resource_id, resource_type, date_created, created_by)
SELECT inserted.resource
inserted.type,
inserted.creation_date,
inserted.username
FROM inserted
End

SET NOCOUNT OFF|||rajeshpatel gave you probably the nicest sollution. If you want to hold on to your own script for some reason, I filtered some errors out of it. This is what it should look like:

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
BEGIN
Declare @.resource_id int
, @.type varchar(100)
, @.date_logged datetime
, @.created varchar(100)

select @.resource_id = inserted.resource
, @.type = inserted.type
, @.date_logged = inserted.creation_date
, @.created = inserted.username

Insert into table_A
(resource_id, resource_type, date_created, created_by)
values
(@.resource_id,@.type, @.date_logged, @.created)
...
END

Gr,
Yveau

Insert records while purging

Not sure if this is possible, seems like it should be with
the right locking mechanism.
I'm purging the oldest dated records from a fairly large
table, and want to be able to insert a new record. The new
record would have a current date(GetDate()). My first
tests are not going well. I can't insert the record at
all. So I am a bit confused as to why. Shouldn't I be able
to insert a record while a purge/delete is occuring if the
records are at opposite ends of the clustered index?
Any help is much appreciated.
Thanks,
JamesBefore an insert, the engine doesn't pre-determine what the value for a
column will be in order to determine whether or not it would be affected by
any existing queries. This is true for constants, variables, computed
columns and, yes, even those with defaults (because you could override the
default). So, rather than risk deciding between an insert and a delete for
a particular row that overlaps under both queries, it blocks inserts until
the delete is finished.
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||If your table is properly indexed, you should be able to control how many
rows you need to delete each time without locking up the whole table. As
long as you don't ending locking the whole while you are doing your delete,
you should be able to insert the new row.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <bigg_game_james@.hotmail.com> wrote in message
news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
> Not sure if this is possible, seems like it should be with
> the right locking mechanism.
> I'm purging the oldest dated records from a fairly large
> table, and want to be able to insert a new record. The new
> record would have a current date(GetDate()). My first
> tests are not going well. I can't insert the record at
> all. So I am a bit confused as to why. Shouldn't I be able
> to insert a record while a purge/delete is occuring if the
> records are at opposite ends of the clustered index?
> Any help is much appreciated.
> Thanks,
> James|||Quick question about this issue.
In my purge routine, I break up the purge by only deleting
a fraction of the records between commits. If an insert
has been executed, shouldn't it get to execute between the
delete statements? Do I need manually escalate a lock for
that insert statement?
>--Original Message--
>Before an insert, the engine doesn't pre-determine what
the value for a
>column will be in order to determine whether or not it
would be affected by
>any existing queries. This is true for constants,
variables, computed
>columns and, yes, even those with defaults (because you
could override the
>default). So, rather than risk deciding between an
insert and a delete for
>a particular row that overlaps under both queries, it
blocks inserts until
>the delete is finished.
>
>"James" <bigg_game_james@.hotmail.com> wrote in message
>news:035c01c39a7d$6bed7720$a501280a@.phx.gbl...
>> Not sure if this is possible, seems like it should be
with
>> the right locking mechanism.
>> I'm purging the oldest dated records from a fairly large
>> table, and want to be able to insert a new record. The
new
>> record would have a current date(GetDate()). My first
>> tests are not going well. I can't insert the record at
>> all. So I am a bit confused as to why. Shouldn't I be
able
>> to insert a record while a purge/delete is occuring if
the
>> records are at opposite ends of the clustered index?
>> Any help is much appreciated.
>> Thanks,
>> James
>
>.
>|||> In my purge routine, I break up the purge by only deleting
> a fraction of the records between commits. If an insert
> has been executed, shouldn't it get to execute between the
> delete statements?
Yes. If it's being blocked, it should be able to jump in between your
delete batches (assuming they are also committed individually).|||So I was definitely on the right track. I had the design
right, but accidently had set implicit_transactions to ON,
once I turned it off, as I originally intended, my inserts
were allowed through.
Thanks for the quick responses.
James
>--Original Message--
>> In my purge routine, I break up the purge by only
deleting
>> a fraction of the records between commits. If an insert
>> has been executed, shouldn't it get to execute between
the
>> delete statements?
>Yes. If it's being blocked, it should be able to jump in
between your
>delete batches (assuming they are also committed
individually).
>
>.
>

Insert Problem

I am fairly new to SQL and have been given a horrible task. I have 2 tables, table 1 is eng_routing and table 2 routing the rows in table 1 are
engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 30 now machine here
1 40 end

the rows in table 2 are

part opnumber rdesc
1 10 goodsin
1 20 treat
1 30 final

the task I have been given is to insert the ops from table 2 into table 1 where engpart=part and where table 1 has a treatments, but the data from table 2 needs to be inserted at the next number after treatments (in this example opnumber 21 and so on) for all opnumber and rdesc,so the outcome would look like

engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 21 goodsin
1 22 treat
1 23 final
1 30 now machine here
1 40 end

I have been given the following code but an error occurs on line 7 on keyword 'where'

use efacdb
begin tran
INSERT INTO eng_routing
SELECT routing.part, TBL3.engopnumber + (SELECT count(opnumber) FROM routing T1
Where T1.part = T.Part AND T1.opnumber<=T.opnumber ) opnumber ,routing.rdesc FROM routing T
INNER JOIN (SELECT engpart,engopnumber,engrdesc FROM eng_routing
WHERE eng_routing.engrdesc= 'treatments') TBL3 where T.part=TBL3.engpart

select * from eng_routing
--commit tran
--rollback tran

Any help would be gratefully recieved--hope this is what you are after, if not my appologies

--first select distinct opnumber into temp table
select distinct opnumber into #c from #b

--now you should be able to do your insert from a select statement like
--this
SELECT b.part, a.engopnumber + (SELECT count(c.opnumber) FROM #c c
Where b.opnumber>=c.opnumber ) opnumber ,b.rdesc FROM eng_routing a, routing b
where a.engrdesc= 'treatments'
order by opnumber|||Thanks for your quick reply. The code falls over on line 1 Invalid object name #b. Thanks for your help|||if you cant figure to replace #b with routing