Reg:Passing Parameter to Trigger

Hello
Is there any possibilities to pass parameters to Triggers if so How?.
Siva.No, what is the purpose?
"SivaprakashShanmugam" <SivaprakashShanmugam@.discussions.microsoft.com>
wrote in message news:52258289-70A2-45EE-8752-44A9E58A2F8D@.microsoft.com...
> Hello
> Is there any possibilities to pass parameters to Triggers if so How?.
> Siva.|||I dont think it is possible.
Triggers can use 2 system tables inserted and deleted to see what rows where
affected on the table.
Best regards
Mikael
"SivaprakashShanmugam" wrote:

> Hello
> Is there any possibilities to pass parameters to Triggers if so How?.
> Siva.|||You cannot send data as parameters to triggers, you can, however, create a
global temporary table outside the trigger, insert the data into that table,
then retrieve the data in the trigger as appropriate.
But most importatntly (as the other poster have already asked) - why?
ML
http://milambda.blogspot.com/sql

Referential Integrity - Sequel Server 7.0

Hi,

i wrote the following trigger to enforce referential integrity:

CREATE TRIGGER [RefIntegrity] ON [Erf] <- Master table
FOR UPDATE AS

if update(ErfNo) or update(Township) <- Primary keys in master
begin
Update ErfPlan <- Child table
Set ErfPlan.ErfNo = i.ErfNo , ErfPlan.Township = i.Township
From ErfPlan a, Inserted i, Deleted d
Where a.ErfNo = d.ErfNo and a.Township = d.Township
end

i am using VB6.0. When my program updates one of the primary keys i get an error :
Key column information is insufficient or incorrect. Too many
rows were affected by update.

i have designed the ErfPlan table with a RcdID field (Autonumber).

This error ONLY occurs on existing records. If i create a new master and related child record, and update any of the key fields, VB displays no error!the funny thing about the vb error: if i requery the record that had a key value/s changed, i can see that it has been updated (for one-to-one AND one-to-many relationship). what i do not understand is why vb displays the error (Key column information is insufficient or incorrect. Too many rows were affected by update).
this error is only displayed when a master record has a one-to-many relationship, but not in a one-to-one relationship.

referencing inserted and deleted tables with sp_executeSql

Hi everyone. Thanks in advance to anyone who might be able to shed some light on this situation.

I have a trigger in which the following SQL code exists.

SET @.tempInserted = N'SET @.dummy = (SELECT '+@.cftColumnName+' FROM INSERTED)'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output

When the trigger executes I receive the following error message...

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'INSERTED'.

My question is, is it possible to in some way reference the INSERTED and DELETED tables using sp_executeSQL?I was able to replicate your problem:
-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET

create table #Tmp(f1 int, f2 char(1))
go
create trigger TmpTrigger on Tmp
FOR DELETE, INSERT, UPDATE
AS
BEGIN
declare @.tempInserted nvarchar(100)
, @.cftColumnName nvarchar(100)
, @.tempAddress nvarchar(100)
set @.cftColumnName = 'f2'
SET @.tempInserted = N'SELECT @.dummy = ' + @.cftColumnName + ' FROM inserted'
SET @.tempInserted = N'select * From #Tmp'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output
select @.tempAddress
END
go
insert into #Tmp values(2,'B')
insert into Tmp values(1,'A')
select * From Tmp
go
drop table #Tmp
drop table Tmp
go

The only thing I can figure out is that since inserted and deleted are special temp tables they are not available to the new process created during the execution of sp_executeSQL.|||Thanks Paul. I ended up taking a different approach that essentially enabled me to achieve what I was attempting to do. Instead of directly referencing the INSERTED and DELETED tables I first create another set of temp tables to which I copy all the records from INSERTED and DELETED. I can then make a reference to these temporary tables during the execution of sp_executeSQL.

Not perfect but it works =).|||A workable solution is better than nothing working! You can always go back and change your code once everything runs end to end.

reference for newbie on trigger and store procedure

hi, good day, i new in trigger and store procedure, can someone please suggest some advice and reference material in order to mastering trigger and store procedure ? thank youstart with sql books online.

Refer to ROWGUID col in update trigger

When writing an Update trigger on a table that has a UniqueIdentifier as the
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>

reeeeeally easy for you guys!

can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)

CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted

insert into tablename2 values (@.value1)

Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,

Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?

The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.

This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table

use northwind
go

CREATE TABLE people
(people_id int)
go

CREATE TABLE Pay
(pay_id int,
people_id int)
go

CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int

select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted

insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go

INSERT INTO people
VALUES (1)

INSERT INTO people
VALUES (2)

INSER INTO people
VALUES (5)

SELECT * from people
SELECT * from pay

DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:

INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3

...or any other set based operation?