Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sproc wont work with trigger

Author  Topic 

jaskalirai
Starting Member

31 Posts
Posted - 2007-11-23 : 12:44:34
USE [dw_manager]
GO
/****** Object: StoredProcedure [dbo].[usp_staff] Script Date: 11/08/2007 08:34:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[usp_staff]


(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'staff')

CREATE TABLE [dw_build].[dbo].[staff] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ann__leave__days] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[call_number] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_eng__size__cc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_make] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_make_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_model] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_model_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_reg__no] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[case_supervisor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[central_code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cons_team] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cons_team_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[controller] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cps_transfer__psion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[daily_overbook] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_in_team] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_left] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_of_birth] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[disabled] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dummy_staff] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[field_staff__psion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[former_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[full_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[grade] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[grade_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[home_to_base_miles] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hours_per_week] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_staff_re_multi_atte] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mandatory_contact_time_o] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[monthly_overbook] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[non_booked_after] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[non_booked_month] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[non_booked_week] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[occupation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[occupation_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[operator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[palmtop_use] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pds_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[personnel_no] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[preferred_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reason_left] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reason_left_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[recruit__sour] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[recruit__sour_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reg__date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sds_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[specialty] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[specialty_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[staff_title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[team] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[team_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[transfer_staff__psion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ukcc_expiry] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ukcc_pin_no] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unspecified_staff] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_11] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_12] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_13] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_14] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_15] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_16] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_17] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_18] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_19] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_20] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_21] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_22] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_23] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_24] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_25] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_26] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_27] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_28] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_6] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_7] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_8] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_9] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[usual_base] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[usual_base_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[weekly_overbook] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

END

use dw_build
go

alter trigger trg_CheckDates
on staff
after update
as

update staff
set date_in_team = '1/1/1800'
where isdate(date_in_team) <> 1
and date_in_team is not null

update staff
set ukcc_pin_no = '1/1/1800'
where isdate(ukcc_pin_no) <> 1
and ukcc_pin_no is not null

update staff
set ukcc_expiry = '1/1/1800'
where isdate(ukcc_expiry) <> 1
and ukcc_expiry is not null

update staff
set updated = '1/1/1800'
where isdate(updated) <> 1
and updated is not null

update staff
set reg__date = '1/1/1800'
where isdate(reg__date) <> 1
and reg__date is not null

update staff
set date_of_birth = '1/1/1800'
where isdate(date_of_birth) <> 1
and date_of_birth is not null


IF @TableType is NULL OR @TableType = 2

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'staff_cl')


CREATE TABLE [dw_build].[dbo].[staff_cl](
[_id] [int] NULL,
[address_1] [varchar](25) NULL,
[address_2] [varchar](25) NULL,
[ann__leave__days] [int] NULL,
[call_number] [varchar](25) NULL,
[car_eng__size__cc] [int] NULL,
[car_make] [varchar](7) NULL,
[car_make_userno] [int] NULL,
[car_model] [varchar](7) NULL,
[car_model_userno] [int] NULL,
[car_reg__no] [varchar](8) NULL,
[case_supervisor] [int] NULL,
[central_code] [varchar](10) NULL,
[code] [varchar](4) NULL,
[cons_team] [varchar](7) NULL,
[cons_team_userno] [int] NULL,
[controller] [int] NULL,
[county] [varchar](25) NULL,
[cps_transfer__psion] [bit] NOT NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[daily_overbook] [int] NULL,
[date_in_team] [datetime] NULL,
[date_left] [datetime] NULL,
[date_of_birth] [datetime] NULL,
[disabled] [bit] NOT NULL,
[dummy_staff] [bit] NOT NULL,
[field_staff__psion] [bit] NOT NULL,
[forename] [varchar](25) NULL,
[former_name] [varchar](25) NULL,
[full_name] [varchar](25) NULL,
[grade] [varchar](7) NULL,
[grade_userno] [int] NULL,
[home_to_base_miles] [int] NULL,
[hours_per_week] [datetime] NULL,
[mail_staff_re_multi_atte] [bit] NOT NULL,
[mandatory_contact_time_o] [bit] NOT NULL,
[monthly_overbook] [int] NULL,
[non_booked_after] [int] NULL,
[non_booked_month] [int] NULL,
[non_booked_week] [int] NULL,
[notes] [varchar](max) NULL,
[occupation] [varchar](7) NULL,
[occupation_userno] [int] NULL,
[operator] [int] NULL,
[palmtop_use] [bit] NOT NULL,
[pds_id] [varchar](25) NULL,
[personnel_no] [varchar](10) NULL,
[postcode] [varchar](10) NULL,
[preferred_name] [varchar](25) NULL,
[reason_left] [varchar](7) NULL,
[reason_left_userno] [int] NULL,
[recruit__sour] [varchar](7) NULL,
[recruit__sour_userno] [int] NULL,
[reg__date] [datetime] NULL,
[sds_id] [varchar](25) NULL,
[specialty] [varchar](7) NULL,
[specialty_userno] [int] NULL,
[staff_title] [varchar](10) NULL,
[surname] [varchar](25) NULL,
[team] [varchar](7) NULL,
[team_userno] [int] NULL,
[telephone] [varchar](25) NULL,
[town] [varchar](25) NULL,
[transfer_staff__psion] [bit] NOT NULL,
[type] [varchar](7) NULL,
[type_userno] [int] NULL,
[ukcc_expiry] [datetime] NULL,
[ukcc_pin_no] [datetime] NULL,
[unspecified_staff] [bit] NOT NULL,
[updated] [datetime] NULL,
[updator] [int] NULL,
[user_1] [bit] NOT NULL,
[user_2] [bit] NOT NULL,
[user_3] [bit] NOT NULL,
[user_4] [bit] NOT NULL,
[user_code_type_1] [bit] NOT NULL,
[user_code_type_10] [bit] NOT NULL,
[user_code_type_11] [bit] NOT NULL,
[user_code_type_12] [bit] NOT NULL,
[user_code_type_13] [bit] NOT NULL,
[user_code_type_14] [bit] NOT NULL,
[user_code_type_15] [bit] NOT NULL,
[user_code_type_16] [bit] NOT NULL,
[user_code_type_17] [bit] NOT NULL,
[user_code_type_18] [bit] NOT NULL,
[user_code_type_19] [bit] NOT NULL,
[user_code_type_2] [bit] NOT NULL,
[user_code_type_20] [bit] NOT NULL,
[user_code_type_21] [bit] NOT NULL,
[user_code_type_22] [bit] NOT NULL,
[user_code_type_23] [bit] NOT NULL,
[user_code_type_24] [bit] NOT NULL,
[user_code_type_25] [bit] NOT NULL,
[user_code_type_26] [bit] NOT NULL,
[user_code_type_27] [bit] NOT NULL,
[user_code_type_28] [bit] NOT NULL,
[user_code_type_3] [bit] NOT NULL,
[user_code_type_4] [bit] NOT NULL,
[user_code_type_5] [bit] NOT NULL,
[user_code_type_6] [bit] NOT NULL,
[user_code_type_7] [bit] NOT NULL,
[user_code_type_8] [bit] NOT NULL,
[user_code_type_9] [bit] NOT NULL,
[usual_base] [varchar](7) NULL,
[usual_base_userno] [int] NULL,
[weekly_overbook] [int] NULL
) ON [PRIMARY]

END


where am i going wrong help please


visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2007-11-23 : 12:52:58
alter trigger trg_CheckDates
on staff
after update
as....


Is this an existing trigger? if no, then use create trigger
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site