SQL Trigger Functions...

[GDX]

Problem:

I have 2 tables: Clients and Client_Entries, They both have a field called ClientID, Here the how the tables are formed:

Clients
-------------
ClientID
Password
Name
Email

Client_Entries
-------------
ClientID
Date_Started
Description

Now what I am trying to do is when i create a new CLientID in the table "Clients" I want to be able to add an entry info "Client_Entries" with the ClientID, The date and a description saying "Project started". Any idea how I might go about doing this? Someone said to use SQL Triggers but I have no clue how to create one can someone please help me out here? I have access to the trigger properties within SQL2K, This is what I get when i open it:
--------------------------
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Clients]
For INSERT, UPDATE, DELETE
AS
--------------------------

But thats it any idea on how do to go about scripting it? I need an example! I have no clue why there is an "AS" please help me out someone thanks a lot!


Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[Oakyman]

This might help

http://sunsite.net.edu.cn/tutorials/seusql/ch14.htm#Chapter

 

[Kirilla]

Hi.

My suggestion is Create a View table, which contains the following columns:

Clients.ClientID,Client_Entries.ClientID AS ClientID_Entr,
Clients.Password, Clients.Name, Clients.Email, Client_Entries.Date_Started, Client_Entries.Description.

If you create this View table you have to use only one query to insert and update datas in both table. (If you want to be sure, there won't be mixed ID-s, you can make an RelationShip between the tables in Database Diagram menu)

regards, Kirilla
tibor.kircsi@scala.hu

 

[nj5j]

This example uses a trigger that handles multi-row or single row updates and calls a stored procedure that inserts rows into another table:
/****** Object: Trigger dbo.TriggerInsert_IOM Script Date: 2/9/2001 1:24:13 PM ******/
CREATE TRIGGER [TriggerInsert_IOM] ON [OT_OR_Order]
FOR INSERT
AS

if (@@ROWCOUNT = 0)
return

declare @tv_OrderID int
declare @tv_LinxID varchar(50)

/* @tv_TableName, */
select
@tv_OrderID = inserted.OR_OrderID,
@tv_LinxID = inserted.SiebelOrderID
from inserted

/* Calll the stored procedure that audits the insert IF data exists in the field*/
if not (@tv_LinxID = '')
begin
exec sproc_Audit_IOM_Linx @tv_OrderID, @tv_LinxID
end

return




GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.TriggerUpdate_IOM Script Date: 2/9/2001 1:24:13 PM ******/
/*
Trigger created by Carl R. Perkins 08 Feb 2001
*/

CREATE TRIGGER [TriggerUpdate_IOM] ON [OT_OR_Order]
FOR UPDATE
AS

declare @t_RowCount int
set @t_RowCount = @@ROWCOUNT

if (@t_RowCount = 0)
return

declare @tv_old_OrderID int
declare @tv_new_OrderID int
declare @tv_old_LinxID varchar(50)
declare @tv_new_LinxID varchar(50)

if (@t_RowCount = 1)
begin
select
@tv_old_OrderID = deleted.OR_OrderID,
@tv_old_LinxID = deleted.SiebelOrderID
from deleted

select
@tv_new_OrderID = inserted.OR_OrderID,
@tv_new_LinxID = inserted.SiebelOrderID
from inserted

/* Call the stored procedure that audits the insert IF data exists in the field AND it was updated*/
if update(SiebelOrderID)
begin
if not (@tv_new_LinxID = '')
begin
if not (@tv_new_LinxID = @tv_old_LinxID)
begin
exec sproc_Audit_IOM_Linx @tv_new_OrderID, @tv_new_LinxID
end
end
end
return
end

/* OK, we have more than one row so we have to invoke cursors for both pseudo-tables inserted/deleted */

Declare Cursor_Deleted_Rows Cursor Fast_Forward For
select
OR_OrderID,
SiebelOrderID
from deleted

Declare Cursor_Inserted_Rows Cursor Fast_Forward For
select
OR_OrderID,
SiebelOrderID
from inserted

Open Cursor_Deleted_Rows

Open Cursor_Inserted_Rows

Fetch Next From Cursor_Deleted_Rows
Into
@tv_old_OrderID,
@tv_old_LinxID

Fetch Next From Cursor_Inserted_Rows
Into
@tv_new_OrderID,
@tv_new_LinxID

While (@@Fetch_Status = 0)
begin

/* Audit the Linx column ONLY IF IT WAS CHANGED AND is not empty*/
if update(SiebelOrderID)
begin
if not (@tv_new_LinxID = '')
begin
if not (@tv_old_LinxID = @tv_new_LinxID)
begin
exec sproc_Audit_IOM_Linx @tv_new_OrderID, @tv_new_LinxID
end
end
end

Fetch Next From Cursor_Deleted_Rows
Into
@tv_old_OrderID,
@tv_old_LinxID

Fetch Next From Cursor_Inserted_Rows
Into
@tv_new_OrderID,
@tv_new_LinxID

end


close Cursor_Deleted_Rows
close Cursor_Inserted_Rows

deallocate Cursor_Deleted_Rows
deallocate Cursor_Inserted_Rows

return
GO



Here is the stored procedure:
/****** Object: Stored Procedure dbo.sproc_Audit_IOM_Linx Script Date: 2/9/2001 1:29:00 PM ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[sproc_Audit_IOM_Linx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sproc_Audit_IOM_Linx]
GO

SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sproc_Audit_IOM_Linx Script Date: 2/9/2001 1:29:00 PM ******/
/*sproc_Audit_IOM_Linx
Author: Carl R. Perkins 08 Feb 2001
Called from trigger ties to table OT_OR_Order
*/

Create Procedure dbo.sproc_Audit_IOM_Linx
@sparm_OrderID int = null,
@sparm_LinxOrderID varchar(50) = null

AS
insert into Audit_IOM_Linx_IPS
(
AILI_DateTime,
AILI_OrderID_IPS,
AILI_OrderID_Linx

)values
(
getdate(),
@sparm_OrderID,
@sparm_LinxOrderID
)
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sproc_Audit_IOM_Linx] TO [ipsuser]
GO

GRANT EXECUTE ON [dbo].[sproc_Audit_IOM_Linx] TO [vitriauser]
GO

GRANT EXECUTE ON [dbo].[sproc_Audit_IOM_Linx] TO [integration]
GO