December 01, 2020, 08:40:57 pm
News: GalahTech Community Based Technical Support!
Pages: [1]   Go Down
Author Topic: Trigger Happy MS SQL Server 2005 (Express)  (Read 3672 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: January 18, 2008, 03:12:37 pm »

Ok so I have a table where data is stored and I have another table that assigns the data to some other bit of data. What I want is that when the data is inserted into the storage table that a record with the newly created id (identity specification) in the assignment table.

All I can guess is something like this:
Code: (sql)
CREATE TRIGGER assignData
ON storage_files
AFTER INSERT
AS
BEGIN
INSERT INTO assignments (gal_id, img_id)
VALUES (1, inserted.img_id)
END

The gal_id is default 1 kind of an unsorted thing. So is this correct?
Posted on: January 18, 2008, 04:07:43 pm
Apparently not as I got an error trying to create it.

The error said I can't use a column name, and pointed to the inserted.img_id which is rather annoying Sad
Logged

-- signature --

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #1 on: January 21, 2008, 09:23:02 am »

Ok I finally figured out how to do this.

I'm using this SQL to build the trigger
Code: (sql)
USE [imgsto]
GO
/****** Object:  Trigger [dbo].[assignUnsorted]    Script Date: 01/21/2008 09:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[assignUnsorted]
   ON  [dbo].[storage_files]
   AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO catalog_assignments (catalog_id, img_id) VALUES (1, IDENT_CURRENT('storage_files'))
    -- Insert statements for trigger here
END

The problem was learning how to use the IDENT_CURRENT function. Most of the websites I found it on were explaining it wrong. I finally went to MSDN online and found the proper way to use the function (kind of) and just played with it until it worked Wink

Now I just need a way to remove the record added to the second table by the trigger when the corresponding record is removed from the primary table.
Logged

-- signature --

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #2 on: January 21, 2008, 05:01:57 pm »

I know what you mean but I cannot recall how to set it up at the moment, maybe this will help:
http://en.wikipedia.org/wiki/Referential_integrity
Logged

                 ..... .....

Pages: [1]   Go Up
Print
 
Jump to: